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 have a measure which counts the number of transactions per id within a timeframe (from slicer):
mFrequency = IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = BLANK(), 0,
IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = 1, 1,
IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = 2, 2,
IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = 3, 3, 4))))
Which gets me output like this:
Id mFrequency
111 1
222 1
333 3
444 2
555 1
Now I need to count up the measure (mFrequency) to get results which look like this, and change based on the slicer timeframe:
mFrequency Count
1 3
2 1
3 1
4 0
I'm new to Power BI. Help is appreciated. Thanks.
Solved! Go to Solution.
Hi,
since you need frequency on the axis, you need to add a stand-alone table with the neccessary values. And based on those values, count the number of IDs with corresponding frequency.
This measure would look like this
Measure = VAR _count = COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( 'Table'[id] ); "@freq"; VAR _trans = CALCULATE ( SUM ( 'Table'[transactions] ) ) RETURN IF ( _trans > 3; 4; _trans ) ); [@freq] = SELECTEDVALUE ( Frequency[F] ) ) ) RETURN IF ( ISBLANK ( _count ); 0; _count )
Since you have not provided any data or sample report, I have created a mockup dataset and created a demo report based on that dataset: pbix
cheers,
S
Hi,
since you need frequency on the axis, you need to add a stand-alone table with the neccessary values. And based on those values, count the number of IDs with corresponding frequency.
This measure would look like this
Measure = VAR _count = COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( 'Table'[id] ); "@freq"; VAR _trans = CALCULATE ( SUM ( 'Table'[transactions] ) ) RETURN IF ( _trans > 3; 4; _trans ) ); [@freq] = SELECTEDVALUE ( Frequency[F] ) ) ) RETURN IF ( ISBLANK ( _count ); 0; _count )
Since you have not provided any data or sample report, I have created a mockup dataset and created a demo report based on that dataset: pbix
cheers,
S
Thanks so much! It worked perfectly.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |