Creating a measure as a Calculated Column

[Anonymous]

Hi PowerBI guys,

I have scatter graph with 3 variables - x, y (x and y are irrelvant to the question) with the size determined by a calculated measure.

The scatter graph is also grouped at the detail of a Member Surname.

I have then asked for conditional formatting to be applied, based on the Open Agreement measure. The colours are grouped  into 3 buckets - less than 20, 20 to 80, and 80+ using conditional formatting:

Everything looks good so far, but the problem is i would like to show these traffic lights as a legend in my graph.

From what i understand, in order to show this as a legend, i would need to have a calculated column in the "legend" section, with these rules implemented. However I cannot build a calculated column from a measure, as measures happen last in processing.

Open_Agreements = CALCULATE(

COUNT(DimMatter[DimMatter_Key]),

FILTER(Agreements, Agreements[Last_End_Result_Date] = blank())

)

What i've tried (this does not produce the right stats)

MOH_Status =

COUNTAX(

FILTER(

GROUPBY(Agreements,Agreements[Max_Allocated_Party]),

Agreements[Last_End_Result_Date] = blank()

),

Agreements[DimMatter_Key] )

Does anyone have any ideas?

Thanks

Oneiricer

[Anonymous]

Hi @Anonymous,

I have created a pbix by taking random data on your problem. I am attaching PBIX file go through it and revert me if any concern.

Here the link:  https://drive.google.com/file/d/1kNjmFrpBK39IT5Mc9HiceT2-H8BCrXcz/view?usp=sharing

Best Regards,

Ravi

[Anonymous]

Hi Ravi

Thanks for responding back so quickly. This is very close, but what i'm after is something like this:

Does a count against "Name" and sees how many are blank per person.

The next step (which i can do) is 1 = red, 2 = green, for example.

[Anonymous]

...

[Anonymous · Accepted solution]

Hi  @Anonymous,

Here is the next step as you described. Please follow the link below and revert me if any concern.

Here you go: https://drive.google.com/file/d/1KwNAty0ncy9GG0QbWw2fylQHX8GeW6rW/view?usp=sharing

Best Regards,

Ravi

[Anonymous]

Thanks a lot. Works fine for me.

Is there a way to have a single DAX code as a solution, instead of creating a secondary table via summarize?

[Anonymous]

Yes There is way but Creating summarize table is much reliable. Though you can write SQL I mean to say that if you calculate count calculation in database then import in power bi then just you have to drag and drop in power bi.
😊