Forum Discussion
Self Join with filter using DAX (not in Power Query)
I'm trying to create a measure using DAX to count distinct values from a Dim table with filter.
Objective: Count Distinct Signee from Dim between valid dates of given current date. These counts will be against each DocKey in the fact table.
Table structure as follows joined on DocKey.
Expected result:
Any help will be highly appreciated.
Thank you
Hi Anonymous ,
If I understand you, correctly, try below. This matches your expected results, but does not include anything based on dates. Please define what you expect from the calendar, because I am going off the rows that you highlighted with the two different colors. If this is not correct, perhaps add a correct picture?
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielDistinct Count of Signee by DocNo = VAR _curDocKey = MAX ( 'Fact'[DocKey] ) // Gets the current row value for DocKey VAR _curDocNo = CALCULATE ( MAX ( DIM[DocNo] ), FILTER ( DIM, DIM[DocKey] = _curDocKey ) ) //Based on _curDocKey gets the DocNo VAR _calc = CALCULATE ( DISTINCTCOUNT ( DIM[Signee] ), DIM[DocNo] = _curDocNo ) //filters table based on _curDocNo and returns cdistinct count RETURN _calc
5 Replies
Hi Anonymous , the results that you show us, cannot come from your data. For example both doc 880 and 990 have only one signee in the table. Are you trying to gather all the signees in 800s docs, and all the docs that are in the 900 range?
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielHi Nathaniel_C
Appreciate your time and let me explain it more.
1st record of fact table (DocKey 880) will find the corrsponding record (DocNo 100) in Dim, next step is to count distinct Signee against the DocNo 100 in Dim within validfrom and validto dates, given the current date. For this, Signee on record 2 and 3 will be counted resulting in count 2.
Same goes for next fact record (DocKey 990) for DocNo 300, Signee on record 4, 6 and 7 will be counted resulting in count 3.
Its like self joining Dim table.
Hi Anonymous ,
If I understand you, correctly, try below. This matches your expected results, but does not include anything based on dates. Please define what you expect from the calendar, because I am going off the rows that you highlighted with the two different colors. If this is not correct, perhaps add a correct picture?
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielDistinct Count of Signee by DocNo = VAR _curDocKey = MAX ( 'Fact'[DocKey] ) // Gets the current row value for DocKey VAR _curDocNo = CALCULATE ( MAX ( DIM[DocNo] ), FILTER ( DIM, DIM[DocKey] = _curDocKey ) ) //Based on _curDocKey gets the DocNo VAR _calc = CALCULATE ( DISTINCTCOUNT ( DIM[Signee] ), DIM[DocNo] = _curDocNo ) //filters table based on _curDocNo and returns cdistinct count RETURN _calc