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.
Hello there
This is probably another question for help replacing the COUNTIFS formula with DAX.
I've tried a few similar solutions posted on here and even tried Grouping my Table etc but with no success.
What I want is to distinct count the PRPcode only within the last 6 months in the table below:
PRPcpde JNLCODE Date
CBL1210067 123 01/01/2016
CBL1210067 1234 12/01/2015
CBL1210067 12345 05/01/2016
CBL1210067 11235 02/04/2015
I.e the answer should be 2
In addition I'm also having trouble working out the1/COUNTIFS DAX?? (no date range required)
I.e for the above table the value of each row should be 0.25
Many thanks in advance for your help!
MG
Solved! Go to Solution.
Hi MG
M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(Today(),-6,Month))
or
M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(max(date),-6,Month))
Try this
@Anonymous
In this scenario, if you want to distinct count the PRPcode only within the last 6 months, you can create measures with following formulas. To count the PRPcode instead of distinct count, you can replace “DISTINCTCOUNT” with “COUNTA” in following formulas.
DistinctCount_Of_PRPcode_Within_Last_6_Months(From Today) =
CALCULATE (
DISTINCTCOUNT ( Table1[PRPcode] ),
DATESINPERIOD(Table1[Date], TODAY(), -6, MONTH)
)
DistinctCount_Of_PRPcode_Within_Last_6_Months(From Latest Date in Table1) =
CALCULATE (
DISTINCTCOUNT ( Table1[PRPcode] ),
DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date]), -6, MONTH)
)
For 1/COUNTIFS DAX (no date range required), you can also create a measure with following formula.
1/DistinCount_Of_PBRcode = 1 / DISTINCTCOUNT(Table1[PRPcode])
Regards,
@Anonymous
In this scenario, if you want to distinct count the PRPcode only within the last 6 months, you can create measures with following formulas. To count the PRPcode instead of distinct count, you can replace “DISTINCTCOUNT” with “COUNTA” in following formulas.
DistinctCount_Of_PRPcode_Within_Last_6_Months(From Today) =
CALCULATE (
DISTINCTCOUNT ( Table1[PRPcode] ),
DATESINPERIOD(Table1[Date], TODAY(), -6, MONTH)
)
DistinctCount_Of_PRPcode_Within_Last_6_Months(From Latest Date in Table1) =
CALCULATE (
DISTINCTCOUNT ( Table1[PRPcode] ),
DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date]), -6, MONTH)
)
For 1/COUNTIFS DAX (no date range required), you can also create a measure with following formula.
1/DistinCount_Of_PBRcode = 1 / DISTINCTCOUNT(Table1[PRPcode])
Regards,
Thanks to both of you!! Both worked for me! Cheers.
Hi
I'm trying to duplicate the above measure as a new column.
Reason for this is that I want users to See Records within the graph which you cannot currently do with a measure.
I've tried various variations but never get the same figure as the measure.
Any suggestions?
Hi MG
M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(Today(),-6,Month))
or
M = Calculate(DistinctCount(PRPcpde),filter(Date>= Dateadd(max(date),-6,Month))
Try this
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |