Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
power_firm
Frequent Visitor

DAX measure with calculated column and measure as a single measure

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

 

5 REPLIES 5
Nathaniel_C
Super User
Super User

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@power_firm 

 

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.

 

@Nathaniel_C 





Did I answer your question? Mark my post as a solution!

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.

 

TransCountByPeriod = VAR TotalTransaction = CALCULATE(COUNTROWS(Sheet1))
VAR UT = ADDCOLUMNS(VALUES(Sheet1[Systime]), "UTP", HOUR(Sheet1[Systime]))
VAR TransPeriod = ADDCOLUMNS(UT, "TimePeriod",
IF([UTP] >= 0 && [UTP] < 11, "Early AM",
IF([UTP] >= 11 && [UTP] < 17, "Afternoon PM",
IF([UTP] >= 17 && [UTP] < 20, "Early Evening PM",
"Evening"))))
VAR Summary = SUMMARIZE(TransPeriod, [TimePeriod], "UniquePeriod", COUNTROWS(Sheet1))
VAR Results = ADDCOLUMNS(Summary, "Result", [TimePeriod] & " " & [UniquePeriod])
RETURN
IF(TotalTransaction > 0,
    CONCATENATEX(
        SELECTCOLUMNS(Results,
            "Result", [Result]
    ),
    [Result],
    CONCATENATE(" ", UNICHAR(10))
)
)
Mariusz
Community Champion
Community Champion

Hi @power_firm 

 

Are you trying to return a table?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors