Measure showing error when nothing is selected in filter

[Anonymous]

Hello Community..

On my sheet I have 2 filters (cycle, survey) .. then if I am cycle by cycle 1, I want my measure to show me ​​values, if cycle 2 selection and in my survey filter I select 1st. Survey, then that measure must show another value.

It works correctly, but if I do not have any cycle selected, it is where the error appears:

MDXScript (Model) (186,36) Calculation error a measure Filter Selection [Filter Cycle]: A table of multiple values ​​was supplied where a single value was expected.

What I can do??

My measure :

Total Sales = sum(Sales[amount])

FilterCycle = values(Cycle[Name])

FilterSurvey = values(Survey[Name Survey])

New Measure = IF(ISCROSSFILTERED(Cycle[Name]),
SWITCH(TRUE(),
[FilterCycle="Cycle1",[Total Sales],
IF(ISCROSSFILTERED('Survey'[Name Survey]),
SWITCH(TRUE(),
[Filter Select]="1er. Survey",80720,
[Filter Select]="2da. Survey",65436,
[Filter Select]="3er. Survey",88714,
[Filter Select]="4to. Survey",776,
BLANK()),[Total Sales]
)))

Thx so much..

[jthomson]

I've got something similar in one of my reports where I need to have no more than one value selected, wrapping the entire formula in if(hasonevalue(your measure),your measure,blank) works for me but it's a slightly different setup to what you want where you want at least one option selected, rather than at most one option

[Anonymous]

@jthomson thx so much for ur help, but your formula no work's to me 😞

[v-jiascu-msft]

Hi @Anonymous,

Could you please share a dummy pbix file? The relationship between two tables and the model structure should be considered. 

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft here is the example of my data... thx for ur help!! 

https://drive.google.com/open?id=1jFfNB0vMtBq8c06tT-4viEoQYC_CYT_U

🙂

[Anonymous]

someone can help me, I can not do what works the way I want... thx guys

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

There is a page level filter "ID SURVRY"[name]. So the ISCROSSFILTERED('ID SURVEY'[Name]) is always TRUE. I don't know how to get the number. Make changes to the blue part. It would work.

MEASURE FILTER =
IF (
    ISCROSSFILTERED ( CYCLE[Cycle Name] ),
    SWITCH (
        TRUE (),
        [Filter Cycle] = "Cycle2", [Total Sales],
        [Filter Cycle] = "Cycle1", IF (
            HASONEVALUE ( 'ID SURVEY'[Name] ),
            SWITCH (
                TRUE (),
                [Filter Survey] = "Survey 1", 213480,
                [Filter Survey] = "Survey 2", 387865,
                [Filter Survey] = "Survey 3", 218488,
                [Filter Survey] = "Survey 4", 248844,
                BLANK (), 831558,
                BLANK ()
            ),
            831558
        )
    ),
    931558
)

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft Your are the best!!! Thx so much, really, really this solution was amazing 😄

Thx for ur help and time Dale. 

[Anonymous]

I try to usea other forms, but i still can not get it to work.

Please someone can help me plz!!