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.
Hi All,
I am trying to create a single measure which combines the result of a calculated column with a measure. I have done it with a calculated column and measure but it is required that both logic be in a measure.
Calculated Coumn = IF(Tbl[Time]) <= 11, "AM", IF(Tbl[Time]) >= 12 && Tbl[Time] < 20 , "PM", FM))
Measure := CALCULATE(COUNTROWS(Tbl))
The table is as follow:
Time
4/3/2019 1:45
4/4/2019 1:45
4/5/2019 1:45
4/6/2019 1:45
4/3/2019 16:45
4/3/2019 16:45
4/3/2019 16:45
4/3/2019 23:45
4/3/2019 23:45
4/3/2019 23:46
4/3/2019 23:50
The result below:
Label Time Count
AM 1-11 4
PM 12-19 3
FM 20-23 4
Many Thanks
Hi @power_firm ,
Create 3 measures and use SUMX to total each category. I then added the three measures to a multi row card and turned off the Category Labels to get this result.
AM - 4
PM - 3
FM - 4
AM = CONCATENATE("AM - ", SUMX(Table1, IF(Hour(Table1[Time]) <=11, 1)))
PM = CONCATENATE("PM - ", SUMX(Table1, IF(Hour(Table1[Time])>=12 && Hour(Table1[Time])<20, 1)))
FM = CONCATENATE("FM - ", SUMX(Table1, IF(Hour(Table1[Time])>=20, 1)))
No Calculated Columns needed!
If this post helps, consider marking it as the solution.
Proud to be a Super User!
Or if you want one measure, one string: AM - 4 PM - 3 FM - 4 with a space in between.
All Time Periods = CONCATENATE( [AM] & " " & [PM] & " ", [FM]) Works in a Card with the labels turned off.
Proud to be a Super User!
I really appreciate everyone input on the issue, i found an alternative solution coding both the measure and the calculated column in one DAX measure. The measure is a concatenated string that conmbines the measure and calculated column together.
Hi @power_firm
Are you trying to return a table?
Hi @Mariusz
Thanks for the response.
I am trying to return a string as a measure that concatenate the label and count into a single measure. It should be similar to the result below:
LabelwithCount
AM - 4
PM - 3
FM - 4
Thanks
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 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |