Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Parameters in Measures can not affect grouping, or can they?

Based on answers to questions in this forum and my own experiments, I find you can not use a what if parameter in a calculation, only in a Measure. And the use is a Measure is limited.  I believe the limits are based on the fact that aggregation happens first, then the parameter is applied.

 

I found a work around for one case, that effectively computes the Measure as simple calculation  -- no aggregation

 

  Emp Measure = sum(Emp) * (1+ Parameter[rate])

 

This works as needed with tables that show a report at the record level, so sum(Emp) does nothing, also  cases with aggregation.

 

But in another case, nothing works as I need.

 

Here I have data by County with industry detail using 6 digit NAICS codes.

 

NAICS

Naics2

Naics3

Naics4

Description

311930

31

311

3119

Flavoring Syrup and Concentrate Manufacturing

 

I want the report user to select the Naics level:   2,3,4 or 6 digits.  

 

I would rather not create 4 report pages  that only different  by the NAICSn level below

 

CountyCode

NAICSn

Emp

Emp forecast

PA001

3111

1000

1050

PA001

3112

2000

2100

PA001

3113

1500

1575

PA003

3111

2000

2100

PA003

3112

1500

1575

PA003

3113

2000

2100

 

 

Is there any solution to allow dynamic grouping?

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

So your requirement is :

you have a slicer of digits (2,3,4,6)

when user selects 2 in digit slicer, the "Emp forecast" should calcuate based on the same NAICS2 code.

when user selects 4 in digit slicer, the "Emp forecast" should calcuate based on the same NAICS4 code.

 

If so, 

I make a test as below

4.png

to achieve this, 

1. create a new table by enter data, don't connect this table to any other table

5.png

 

2.create measures

n_maincode =
VAR selected_n =
    SELECTEDVALUE ( Table1[digits] )
RETURN
    LEFT ( MAX ( 'main data'[NAICSn] ), selected_n )


sum_per_code =
CALCULATE (
    SUM ( 'main data'[Emp] ),
    FILTER (
        ALL ( 'main data' ),
        [n_maincode] = MAXX ( ALL ( 'main data' ), [n_maincode] )
    )
)

final = [sum_per_code]*(1+[Parameter Value])

Best Regards

Maggie

 

 

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

Anonymous
Not applicable

I tried to solution from   but it does not do what I need.

In this proposed solution you can see the 'sum per code' values are the same in every row, as is 'final'.  This is clearly the wrong sum. 

 

It seems the MAX ( 'main data'[NAICSn] ) and MAXX in the two measure code definitions gives us the maximum NAICSn value for a grouping, before the left operation is applied, which in general won't be right.   This is why 'sum per code' is  3500, as this is the sum of the two NAICSn = 3113 cases (emp = 1550 and 2000). But this sumis not relevant for the Naicsn 3111 and 3112 rows.

 

The problem is clearer when I select  digits 2 and 3=.  In the example table I gave, the digit=2 case  should show as below. Note you need to remove NAICSn as the full digits column to group correctly, only by CountyCode and n_maincode.

 

CountyCoden_maincodeEmpEmp forecast
PA0013145004725
PA0033155005775

 

The problem is the DAX formula produces an error is you remove MAX  and MAXX -- i.e., the formula is not accepted.

 

 

Anonymous
Not applicable

Following up -- here is what I think is the basic problem

 

Suppose I create  column defined as

 

nx =
VAR na = 3
VAR nb = SELECTEDVALUE ( Digits[digits] )
RETURN
LEFT ( Data1[NAICS], na ) 

 

Then using na in the LEFT functions works, but using nb as defined does not show anything in a table 

 

nx =
VAR na = 3
VAR nb = SELECTEDVALUE ( Digits[digits] )
RETURN
LEFT ( Data1[NAICS], nb )   

 

FAILS

 

And there's is no way I see to make a measure that aggregates over NAICS because such aggregation depends on then results of the LEFT. One od thse need to occurr first inthe proder of operations LEFT or aggregator suchn as MAX

 

Am I right that this a limitation of 'columns' -- they can not use selected defined parameters ? 

If so, this seems rather arbirtrary and limiting.

v-juanli-msft
Community Support
Community Support

Hi @Anonymous

1.How do two tables relate?

 

NAICS

Naics2

Naics3

Naics4

Description

311930

31

311

3119

Flavoring Syrup and Concentrate Manufacturing

 

CountyCode

NAICSn

Emp

Emp forecast

PA001

3111

1000

1050

PA001

3112

2000

2100

PA001

3113

1500

1575

PA003

3111

2000

2100

PA003

3112

1500

1575

PA003

3113

2000

2100

 

2."I want the report user to select the Naics level:   2,3,4 or 6 digits.  "

which column should be added in the slicer?  "NAICS" from the first table?

 

Best Regards

Maggie

 

Anonymous
Not applicable

The two tables join by NAICS. At six digit level. 

 

NAICS2 3 & 4 digits can also be derived from the second table using left(NAICS,n).

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.