Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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.
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
Proud to be a Super User!
You are welcome @Anonymous !
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |