Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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..
Solved! Go to 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
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
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
@v-jiascu-msft here is the example of my data... thx for ur help!!
https://drive.google.com/open?id=1jFfNB0vMtBq8c06tT-4viEoQYC_CYT_U
🙂
someone can help me, I can not do what works the way I want... thx guys
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
@v-jiascu-msft Your are the best!!! Thx so much, really, really this solution was amazing 😄
Thx for ur help and time Dale.
I try to usea other forms, but i still can not get it to work.
Please someone can help me plz!!