DAX using count formulas on measures that are text anyone?

[aggiebrown]

I am working with a large dataset and want to avoid adding extra tables and get to end result through measures instead - this will probably need advanced DAX language though.

I have Created a few measures I need for the end result and one of them is calculating date difference from Sales_Date to Cancellation_Date

Now what I want to show at granular level is:

1. Bucket the Cancellation periods - which I did do using VAR but end result then ends up being a Text String

2. Calculate how many Cancellations there were in each of the buckets - somehow I need to count if [Text string] = "Cancelled <30 Days" etc

3. END result is to show a count of Cancelled within [Bucket1], [Bucket2], [Bucket3] / Total_Sales

I have all the needed tables connected as far as I am aware, I would just appreciate some ideas around how to CALC/ turn the TEXT string to a value instead.

Cancellation buckets =

VAR Cancellations = [# Cancellation duration]

RETURN

SWITCH (

TRUE (),

ISBLANK([# SBC Cancellation Duration]), BLANK(),

Cancellations <= 30, "Cancelled <30 Days",

Cancellations <- 60, "Cancelled 31-60 Days",

Cancellations <= 90, "Cancelled 61-90 Days",

Cancellations > 90, "Cancelled 90+"

)

New Measure

0-30 Bucket VALUE CALC = (ISSELECTEDMEASURE([SBC Cancellation buckets]),"Cancelled <30 Days")

that throws an error Operator or expression [] is not supported in this context.

Any thoughts would be appreciated.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please show the result there very clearly.

[aggiebrown]

@Ashish_Mathur please see sample file attached - the bottom table is the result that I would require to see

Sample Data 

[Ashish_Mathur]

Hi,

What is the problem in creating that table.  In fact, haven't you already created it?

[aggiebrown]

@Ashish_Mathur Hi, thanks for your response. I need help with creating a measure that will bucket cancellations, I have all the tables I need.

[Ashish_Mathur]

But you already have created that in the second visual.  What more do you need?  Please be clear.

[aggiebrown]

If you open the file, you will see the measures on second visual, do not produce correct result. I want the measures to count how many Retention plans there have been with cancellations that happened 0-30 days, 31-60 days, 61-90 days etc

[Ashish_Mathur]

Hi,

I think the last matrix visual is what you want.  You may download my PBI file from here.

Hope this helps.

[aggiebrown]

Hey,

Many thanks for your help, but due to the dateset size, I want to avoid creating new tables,  so want to achieve this with measures only, whereas you're added a new table for Cancellation Buckets. 😞 

[amitchandak]

@aggiebrown , You can create a New Measure, but that need to use some grouping , I used Id here. This is usually unsummarized columns/Axis/ legend of visual

0-30 Bucket VALUE CALC = sumx(filter(values(Table[ID]),[Cancellation buckets] = "Cancelled <30 Days"),[# Cancellation duration])

[aggiebrown]

the measure is not part of the table though, so how do I reference table id?