Use DAX to Create a Table within a Measure instead of creating external tables that aren't optimal

[aggiebrown]

Hello everyone, I am fairly new to DAX but working on a large dataset and therefore keen to resolve issues within DAX Measures rather than creating supporting tables. 

I need to calculate nested by period cancellations of certain plan and divide that number by total sales of that said plan. 

I have created a supporting model which looks at my two data tables (Total Sales & Details of Success Plan) and connected the lookup tables I will need to drill down to Individual levels, so Employee_Lookup, Date_Lookup and Calendar_Table.

Worth saying that Both Data tables already have DateKeys in them that contain Emp_Num&&DateKey so the matching should be fairly simple.

I am stuck when it comes to DAX Language itself though, I am thinking, is there a way of creating a table within a DAX Measure that will group the results by Agent ID and by Differnt Buckets and Return a Value of that. I would be happy to have 4 different measures for 4 different buckets I need. But so far I can't even seem to be getting there, as every DAX formula I type is expecting a table reference.... 

I have created a measure to bucket the cancellations as a start, using VAR Function which goes like this to see what my cancellation duration buckets are:

Cancellation buckets =

VAR Cancellations = [#  Cancellations duration]

RETURN

SWITCH (

TRUE (),

ISBLANK([#Cancellations duration]), BLANK(),

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

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

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

Cancellations > 90, "Cancelled 90+"

)

Now what I want to do, is to divide each of these buckets per Total Sales per agent. 

Total sales is a ready measure as well. I am not too sure how to go on about this at all.

I was thinking of creating seperate measures to count how many have been cancelled in each of these buckets, which would be 4 measures, and then another 4 measures to calculate the % of these buckets in the sales.

But even when doing that I ran into a problem (I am guessing it's because the Cancellation buckets measure is a text string and I am just not too sure which function of DAX to use for that. 

Any help would be appreciated.

[Jihwan_Kim]

Hi, @aggiebrown 

I suggest having a grouping table (separated table) like below.

Then, you can create a measure to filter the fact table by the cancellation column.

I tried to create a sample pbix file like below.

Sales Total by group =

SUMX (

FILTER (

VALUES ( sales[Customer] ),

COUNTROWS (

FILTER (

'grouping',

CALCULATE ( SUM ( sales[Cancelled within] ) ) >= 'grouping'[Min]

&& CALCULATE ( SUM ( sales[Cancelled within] ) ) <= 'grouping'[Max]

)

) > 0

),

CALCULATE ( SUM ( sales[Sales] ) )

)

https://www.dropbox.com/s/mjdcmyplj3k4364/aggiebrown.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[aggiebrown]

Thanks for that, that would work, but the dataset is so large I am trying to create measures rather than creating even more tables. 

Is there any way of creating different measure to count different groups / buckets based on text?

[Jihwan_Kim]

Hi, @aggiebrown 

Thank you for your feedback.

It is quite difficult for me to mention without seeing your sample pbix file.

I created my sample pbix file and one way to segment my sample data is to create a grouping table.

Without creating a grouping table, I cannot categorize my sample data, but I can just flag the data.

[aggiebrown]

I appreciate your help on this, the Dataset that I am currently working on is 2GB in size, so not keen on creating even more tables to be refreshed every day for the purpose of one small calculation. :$

What I am looking for is a DAX formula that will allow me to create a Table within the measure, group it by Agent name and by buckets Variations, and return a count as a result. 

That's what I have in my head at least. For this I created a seperate Model within the dataset, that only looks at LineUp (Employee list with DateKeys etc), Account_Lookup and Calendar table. 

My two data tables I need to use for this measure are Success Plan Table and Booking (Sales) Table.

Any thought on how to approach this without creating a table?

[v-yiruan-msft]

Hi @aggiebrown ,

I'm not clear about your requirement, do you want to get the (count number)percentage of per bucket in the overall? Could you please provide some sample data(exclude sensitive data) in Success Plan Table and Booking (Sales) Table and your expected result with specific examples? Thank you.

On the other hand, please review the content in the following links and check whether they can help you.

Aggregation and Summarization in DAX with Power BI

Aggregated Table in Power BI – Using GroupBy Function in DAX

Best Regards

[aggiebrown]

@v-yiruan-msft many thanks for your reply.

Please find below the sample data. The bottom table is the result I will require, I just can't seem to get the right aggregation for it to count.

Your help would be appreciated.

Sample Data 

[amitchandak]

@aggiebrown , seem like you want to use this measure as a dimension, for that you have to create a table with these values and join them with this measure in the filter function of a new measure.

Refer if my video can help on this

https://youtu.be/CuczXPj0N-k

[aggiebrown]

@amitchandak - thanks for your suggestion. I should have probably mentioned in the subject, I have quite a large DataSet I am working on, so am looking at getting to the end result with DAX language rather than extra tables. Any help / ideas would be appreciated 🙂