Slicer parameter in DAX

[SA800029]

Hi,

I'm trying to add a calculated column to a table with a simple formula based on a parameter and a slicer.

My parameter is a simple interger ranging from 70000 to 70999, it represents aircraft production rank.
The table has 3 columns : the key, Minimum and Maximum, which refers to the first and last aircraft where the key is effective.

What I want to do : I want to be able to select an aircraft (using my parameter as a slicer) then see if the selected Aircraft is in the range Minimum-Maximum, for each rows, by adding an "X" if TRUE and nothig otherwise. The end goal being, once the effective rows are tagged, I'll use this column to apply a page wide filter to keep only the effective keys for the selected aricraft. 

I've read several post about using SELECTEDVALUE, my problem is that DAX doesn't seem to compute the value selected so the logical operations fail to determine whether it's True or not.

Here is my formula :

Status = 
VAR
    AC=SELECTEDVALUE('Table Avions'[Table Avions])
RETURN
    IF(MSN_Effectivity[Minimum]<=AC && MSN_Effectivity[Maximum]>=AC;"X";"")

The thing that is boggling me is that I've been able to correctly identify the rows with a Measure, that count the keys where the aforementionned criterions are met, but I cannot use the measure to filter the table : 

Count Selected MSN = 
VAR
    MSN=SELECTEDVALUE('Table Avions'[Table Avions])
RETURN

CALCULATE(
    COUNT(MSN_Effectivity[psbEffectivity]);
        FILTER(ALL(MSN_Effectivity[Minimum]);MIN(MSN_Effectivity[Minimum])<=MSN);
        FILTER(ALL(MSN_Effectivity[Maximum]);MAX(MSN_Effectivity[Maximum])>=MSN)
)

Thank you for your help,

[Greg_Deckler]

Not entirely sure I understand this, but if you are trying to use a dynamic measure in a calculated column you are not going to have success. Calculated columns are calculated at the time of data load so selecting a measure in a slicer is not going to have any impact on your calculated column.

[SA800029]

Hello Greg_Deckler

Does this example help ?

What I want to acheive is just adding an "X" in the rows where 70024 is between Minimum (70016) and Maximum (70034 in the example).
The number "3" comes from the Measure I've been trying to use as an alternative to identify the rows.
It works, since I only have a number when the equation is True, but I cannot use it to filter the table since it's a measure.

That's why I though of a calculated column that I could use latter as a filter.

Does that make more sens ?

[v-cherch-msft]

Hi @SA800029

It seems you may use visual level filter. Below is the post for your reference.

https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/564906#M266573

Regards,

Cherie

[Anonymous]

Hello,

Sorry for answering late, our company has switched account, I had to create a new one, anyway.

Thank you for your advices, I see that my problem wasn't clear. I don't have any trouble filtering the table using a measure, what I need to do is to use this measure to filter my model.
Greg, your formula works fine, but it only applies a visual filter on my table.

Do you think it's possible ?

Here's a picture of (part) of the model if it helps :
"Table Avions" is the parameter used in the slicer
"MSN Effectivity" is the table I've been trying to add the calcultaed column / measure to filter the rest of my model

Thank you,

[Anonymous]

Hi,

For those of you who would encounter the same problem I found a way to get around my problem.

I had to change the table "MSN Effectity" : instead of having a table with : Key / Min range / Max Range, I've duplicated the lines (Key) for each MSN, so I end up with : Key / MSN (where Key "i" is repeated "n" times, "n" being the difference between Min Range and Max Range for the specified Key).

I plugged my column "MSN" to a slicer and it work perfectly.

Thank you for your support and happy new year,

[Greg_Deckler]

I don't know why you can't use a measure to filter a table visual, I can. So, if you create a Measure like:

Status = IF(MAX([My Parameter]) >= MAX([Minimum]) && MAX([My Parameter])  <= MAX([Maximum]),"X",BLANK())

Then just put that into your table visual and filter the measure where Status is X