Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
to achieve this,
1. create a new table by enter data, don't connect this table to any other table
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.
I tried to solution from v-juanli-msft 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.
CountyCode | n_maincode | Emp | Emp forecast |
PA001 | 31 | 4500 | 4725 |
PA003 | 31 | 5500 | 5775 |
The problem is the DAX formula produces an error is you remove MAX and MAXX -- i.e., the formula is not accepted.
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.
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
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).
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |