Self Join with filter using DAX (not in Power Query)

[Anonymous]

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

[Nathaniel_C]

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.
Nathaniel

[Anonymous]

Hi @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.

[Nathaniel_C · Accepted solution]

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.
Nathaniel

Distinct 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

[Anonymous]

Great! It worked.

Thank you @Nathaniel_C 

[Nathaniel_C]

You are welcome @Anonymous !