calculating seniority banking

0
votes

I work on ssas tabular, I use live connexion to connect powerbi to the cube.

I have table Fact_an

enter image description here

banking seniority

enter image description here

Dim_produit

enter image description here

I create a report and a new mausure AN. WHE A USER SELECT A YEAR AND A MONTH AND banking seniority, I need to calculate the count of the rows the last 12 of months banking seniority< banking seniority seelcted by the user:

AN = 

VAR a = SELECTEDVALUE(Dim_DateFicheAgent[ID_DateFicheAgent])
VAR b =SELECTEDVALUE('Seniority banking'[banking seniority])
RETURN

    CALCULATE (
COUNTROWS(FILTER(Fact_AN;

     (Fact_AN[banking seniority]<=b && NOT ISBLANK (Fact_AN[banking seniority]))));
         DATESBETWEEN (
        Dim_DateFicheAgent[ID_DateFicheAgent];
        NEXTDAY ( SAMEPERIODLASTYEAR (LASTDATE ( Dim_DateFicheAgent[ID_DateFicheAgent] ) ));
        LASTDATE ( Dim_DateFicheAgent[ID_DateFicheAgent] )

))

enter image description here

It returns wrong results, how ccan I correct it? Any help will too much appreciated. I put a pbix file example here https://drive.google.com/file/d/1Ja3NevOm6i80uuS6lKPpHIBYaNQ2jee2/view?usp=drivesdk

My problem is for example when I choose seniority less than 5 year

Expected results

enter image description here

But what I get:

enter image description here

Which is wrong.

1
powerbidaxpowerbi-desktopssas-tabular

1 Answers

0
votes

I figure out the problem: the banking seniority slicer is related to the Fact_AN table. What I can do is to create a new table for Seniority slicer that is NOT related to the Fact_AN table and use that new unrelated table for the slicer and in the measure for value of b.