Grouping my transaction with grouping table

DAX DAX Calculations
1

Hi,

About Age grouping, when I have this data :
image

How is the DAX looks like in Calculated Column ? I watched several of Sam’s course but it is with Measures, What would the best for this actually, Measures or Calculated column ?

Btw, the mode I’m using is Direct Query and as Sam’s did, there is no relation between this two table.

Thanks in advance

2

Hi @Toni,

Generally speaking I think it’s safe to say that there is just one major reason for creating a Calculated Column and that is if you need to place the calculated results in a slicer, or see results in rows or columns in a table (as opposed to the values area), or in the axes of a chart.

The value of a calculated column is stored in the data model and is computed during data refresh time and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a table, or axes and filters applied to a chart.

Read more here.

.

For this type of calculation you would use a Measure and for more details on Grouping/Segmentation with supporting tables please see this topic by @Greg

I hope this is helpful

3

Hi Melissa,

Thanks, it is very helpful for me to understand then. Btw, there’s one more thing though, If lets say I do need to create it in Calculated column, is it still possible ? I mean to have a grouping table, since the table is unrelated, Calculated column needs to be related, am I right ?

Like the one in the sample (which actually I saw it before and with other sample as well), they all use ‘fix’ value not by lookup to another table for the group classification value.

Thanks

4

Hi @Toni,

Let me emphasize that creating this value in a Calculated Column is a waste of precious space and you definitely don’t “need” it as a Calculated Column because you can and should do this in a Measure.

But in the end, sure, you can “lookup” that value by iterating over your supporting table…

5

Hi Melissa,

Yes, understand that, as your explanation above, I do need to have a slicers for the grouping in my report, and it will be used in table visual for listing all the orders in that group, for user to filter.
What kind of lookup are you referring to ? Since Lookup is not supported in Direct Query.

Thanks,

6

I’m afraid this is a misunderstanding, you still don’t need to create that attribute in you Orders table because you already have that attribute in your Age Group table. And that is the Dimension you will use for Slicers and Visuals in your Report.

Please review all the content in the Dynamic Grouping topic provided first and if you still have trouble implementing it after that - provide a mockup PBIX including the visuals and slicers you require.

Thanks!

7

Hi Mellisa,

Basically, it is simple as my 1st picture , just need to add the grouping name. however I attached my PBIX for you here, only it is worth to mentioned again, I’m using Direct Query for my report, so there is a different mode in there. As the dimension of the grouping, I’m actually still confuse on what you’re mean, basically I will need to filter my Orders table later on per that Interval selection.

Here is the picture I needed for my visual, the interval name in grouping table needs to be added in my Orders table (the 2nd table, which is the grouping was only for illustration, doesn’t meant to display):

image
image844×512 25.3 KB

PS: there are 2 columns Aging days over there, it is only for checking, since in my report is a measure but I export it out to create this sample.
SampleAgingGroup_Issue.pbix (32.7 KB)

Thanks,

8

Is this what you want:
image

Measure = 
VAR CurrentAge = [Measure aging days]
VAR GetInterval = 
    FILTER ( 
        'Grouping',
        CurrentAge >= 'Grouping'[From]
            && CurrentAge <= 'Grouping'[To]
    )
VAR Result = 
    CONCATENATEX ( GetInterval, 'Grouping'[Interval], ", " )
RETURN
    Result
1 Like
9

Hi @Toni,

In addition to the [Measure] provided by @AntrikshSharma, set a visual level filter on your table to only show items when the value is not blank. Then it will also respond to Slicer selections on the page.

An alternative could be.

Aging Group = 
VAR myDays = [Measure aging days]
VAR myGroup = 
CALCULATE( MAX( 'Grouping'[Interval] ),
    FILTER( 'Grouping',
        myDays >= 'Grouping'[From]
        && myDays <= 'Grouping'[To]
    )
)
RETURN

IF( NOT( ISBLANK( [Measure aging days] )),
    myGroup
)

I hope this is helpful.

10

Hi Melissa,
I’m sorry, I think your measure is not working, at least when I tried it. But it works if I’m using visual filter as you’re mentioned, to “is not blank” to that measure.

I think it is fine, unless there is something else in your measure that I might be wrong and you’d like to help further.

Thanks,

11

Hi @Toni,

If you mean by “not working” returning blanks when a filter on Age Group is active, than there is nothing wrong - that is by design. You have to set that visual level filter to “is not blank” because if you remember there is no relationship with the Age Group table.

I hope this is helpful.

12

Hi @Toni, did the response provided by @AntrikshSharma and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

13

Hi @Toni, we’ve noticed that no response has been received from you since the 24th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

14

Hi @Toni, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!