Forum Discussion
Helper IIIUse DAX to Create a Table within a Measure instead of creating external tables that aren't optimal
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:
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.
8 Replies
- Jihwan_Kim
Super User
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] ) ))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
aggiebrownThanks 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.
- 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
- 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 🙂
