Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Greetings.
Using the following table as an example, I am using two separate measures to make a Distinct Count of the Date column when the Hours column is = 0 (Measure 1) and when the Hours column > 0 (Measure 2). These two measures are subtotals and need to be displayed independently. However, I am overstating the total count by one because November 12 has values that are both = 0 and > 0. A given date should only be counted once. How can I ensure the output counts November 12 only once in these instances?
Current output for Measure 1 = 2
Current output for Measure 2 = 3
Total = 5
Desired output for Measure 1 = 1
Desired output for Measure 2 = 3
Total = 4
ID | Date | Hours |
63813-1 | Monday, November 11, 2019 | 1.7 |
63813-2 | Tuesday, November 12, 2019 | 0 |
63813-3 | Tuesday, November 12, 2019 | 1.2 |
63813-4 | Tuesday, November 12, 2019 | 2.1 |
63935-1 | Sunday, November 17, 2019 | 1.9 |
63935-2 | Sunday, November 17, 2019 | 1.8 |
63935-3 | Sunday, November 17, 2019 | 1.4 |
63935-4 | Monday, November 18, 2019 | 0 |
Many thanks for the assistance.
Solved! Go to Solution.
@DAXHeadroom , Try measures like
countx(filter(summarize(Table, Table[Date], "_1", sum(Table[Hours])),[_1]>0),[Date])
or
countx(filter(summarize(Table, Table[Date], "_1", sum(Table[Hours])),[_1]=0),[Date])
@DAXHeadroom , Try measures like
countx(filter(summarize(Table, Table[Date], "_1", sum(Table[Hours])),[_1]>0),[Date])
or
countx(filter(summarize(Table, Table[Date], "_1", sum(Table[Hours])),[_1]=0),[Date])
Hello @amitchandak
Your solution above worked well - thank you! Now, I am having a similar problem when making additional DISTINCTCOUNTS of other columns. Specifically, I am employing the following two Measures to the below sample data table.
Flight Days Away = CALCULATE(DISTINCTCOUNT('Appended Metrics'[Dept Date Local]),'Appended Metrics'[Leg Hours]>0,'Appended Metrics'[Ver]="-V-",'Appended Metrics'[Local SAV Flights]=0)
Non-Flight Days Away = CALCULATE(COUNTAX(FILTER(SUMMARIZE('Appended Metrics', 'Appended Metrics'[Dept Date Local], "_1", SUM('Appended Metrics'[Leg Hours])),[_1]=0),[Dept Date Local]),'Appended Metrics'[Ver]="-V-", 'Appended Metrics'[Dept AP]<>"KSAV",'Appended Metrics'[Arrive AP]<>"KSAV")
Dept Date Local Dept AP Arrv AP Leg Hours Ver
9/21/2020 KSAV KMMU 1.6 -V-
9/21/2020 KMMU KMMU 0 -V-
9/22/2020 KMMU KMMU 0 -V-
9/22/2020 KMMU KHPN 0.3 -V-
9/23/2020 KHPN KHPN 0 -V-
9/23/2020 KHPN KSAV 1.8 -V-
My output is:
Flight Days Away = 3
Non-Flight Days Away = 2*
Since any given day should be counted once, and Flight Days take precedence,
my desired output is:
Flight Days Away = 3
Non-Flight Days Away = 0
*I'm also confused as to why this is 2 versus 3. Seems odd to me.
Any help woudl be very much appreciated.
Regards,
Hi @DAXHeadroom
Measure 1 =
COUNTROWS( FILTER(DISTINCT(Table1[Date]), CALCULATE(SUM(Table1[Hours])) = 0))
Measure 2 =
COUNTROWS( FILTER(DISTINCT(Table1[Date]), CALCULATE(SUM(Table1[Hours])) > 0))
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |