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.
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
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 |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |