Turning text measure into value DAX

[aggiebrown]

Need help with a simple measure (I think) 

New to DAX (swtiching from EXCEL Formulas gives me a bit of a headache.

[Cancellation buckets] is a measure, not a table. Due to Dataset Size, I am trying to avoid building new tables, etc.

Now I created Cancellation buckets as a measure to nest the Cancellation periods into 4 groups. Now I want to create Measure that will allow me to then count and show Cancellation buckets by count of Cancellations depending on the period.

I am trying to achieve this with something similar to COUNTIF in excel, and whereas it gives me a "1", it does not summarise it at the bottom of the table. 

I'd appreciate any help.

# Cancelled within 30 Days = CALCULATE(IF([Cancellation buckets] = "Cancelled <30 Days",1,BLANK()))

[v-yingjl]

Hi @aggiebrown ,

You can create the measure like this:

# Cancelled within 30 Days = 
VAR tab =
    ADDCOLUMNS (
        ADDCOLUMNS ( 'Table', "CB", [Cancellation buckets] ),
        "Tag", IF ( [CB] = "Cancelled <30 Days", 1, BLANK () )
    )
RETURN
    COUNTX ( tab, [Tag] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aggiebrown]

I am not sure how to uppload the updated sample that you attached, but  I created a new measure in your sample, which goes like this, and that works fine. 

Cancelled #30 Days = 
var tab = 
ADDCOLUMNS
(ADDCOLUMNS('Table', "#30 Days", [Budgets]),"tag", IF( 'Table'[Budgets] <= 30,1, BLANK())
)
RETURN
COUNTX(tab, [tag])

 Based on this how would I create another bucket for "Cancelled 31-60 Days" - using the same calc as I did in the new measure but updating the criteria to greater than 30 or less/equal to 60?

@v-yingjl would much appreciate if you could help

[Ashish_Mathur]

Hi,

In an Excel file, share some data and show the expected result.  Upload the file to OneDrive and share the download link here. 

[v-yingjl]

Hi @aggiebrown ,

Just write the simliar formula like this:

A =
VAR tab =
    ADDCOLUMNS (
        ADDCOLUMNS ( 'Table', ">30&&<60 Days", [Budgets] ),
        "tag",
            IF ( [Budgets] > 30 && [Budgets] <= 60, 1, BLANK () )
    )
RETURN
    COUNTX ( tab, [tag] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aggiebrown]

Hey,

This measure does not work on my dataset. I have created a sample, with the relationships I currenlty have in place.

The desired look is to drill down to how many Ret plans there were, out of which how many cancelled, and cancelled ones bucketted in 3 different groups, however that calc does not work. I think it's because my first two measures are Cross Filtering the two tables and it should follow the same process right?

Example Data File  

[Greg_Deckler]

@aggiebrown Excel to DAX Translation - Microsoft Power BI Community