DAX measure with calculated column and measure as a single measure

[power_firm]

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

[Nathaniel_C]

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.

[Nathaniel_C]

@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 

[power_firm]

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]

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.

[power_firm]

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