cancel
Showing results for
Did you mean:

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

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
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.

Proud to be a Super User!

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.

Proud to be a Super User!

Frequent Visitor

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]))
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))
)
)
Community Champion

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.

Frequent Visitor

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors