Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have a simple measure which intersects my fact table with several disconnected tables and works without issue:
Intersect =
VAR varInter1 = INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas1'[Values] ))
VAR varInter2 = INTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas2'[Values] ))
RETURN
CALCULATE ( sum('Fact'[Amount]),varInter1, varInter2 )
Output:
I would like to dynamically control which types are used via a slicer, making the INTERSECT dynamic.
The code I attempted does not work as CALCULATE is expecting a column filter.
Intersect =
VAR varSelect1 = IF (HASONEVALUE(Treatas1[Type]), VALUES(Treatas1[Type]))
VAR varInter1 = SWITCH ( TRUE (), varSelect1 = "County", INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas1'[Values] )),
varSelect1 = "Town", INTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas1'[Values] )),
varSelect1 = "Road", INTERSECT(ALL('Fact'[Road]), VALUES ( 'Treatas1'[Values] )))
VAR varSelect2 = IF (HASONEVALUE(Treatas2[Type]), VALUES(Treatas2[Type]))
VAR varInter2 = SWITCH ( TRUE (), varSelect2 = "County", INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas2'[Values] )),
varSelect2 = "Town", INTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas2'[Values] )),
varSelect2 = "Road", INTERSECT(ALL('Fact'[Road]), VALUES ( 'Treatas2'[Values] )))
RETURN
CALCULATE ( sum('Fact'[Amount]), varInter1, varInter2 )
I could obviously use a conditional statement before CALCULATE for this example, but the final product will be using up to 10 types with up to 4 combinations which would be an enormous bit of code.
Hi @Kashinoda ,
I think the error is caused of the SWITCH function can't return a column or table, but only a value.
Best Regards,
Community Support Team _ kalyj
@Kashinoda , Try if this can work
Intersect =
VAR varSelect1 = IF (HASONEVALUE(Treatas1[Type]), VALUES(Treatas1[Type]))
VAR varInter1 = SWITCH ( TRUE (), varSelect1 = "County", filter('Fact', 'Fact'[County] in INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas1'[Values] ))),
varSelect1 = "Town", filter('Fact', 'Fact'[Town] in INTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas1'[Values] ))),
varSelect1 = "Road", filter('Fact', 'Fact'[Road] in INTERSECT(ALL('Fact'[Road]), VALUES ( 'Treatas1'[Values] ))))
VAR varSelect2 = IF (HASONEVALUE(Treatas2[Type]), VALUES(Treatas2[Type]))
VAR varInter2 = SWITCH ( TRUE (), varSelect2 = "County", filter('Fact', 'Fact'[County] in INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas2'[Values] ))),
varSelect2 = "Town", filter('Fact', 'Fact'[Town] in IINTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas2'[Values] ))),
varSelect2 = "Road", filter('Fact', 'Fact'[Road] in INTERSECT(ALL('Fact'[Road]), VALUES ( 'Treatas2'[Values] ))))
RETURN
CALCULATE ( sum('Fact'[Amount]), varInter1, varInter2 )
Thanks for your reply, unfortunately this errors with "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".
I uploaded the PBIX here: https://drive.google.com/file/d/1j9watMGIEvBB82nMzP0h3bxJolUD6twl
User | Count |
---|---|
75 | |
75 | |
45 | |
31 | |
27 |
User | Count |
---|---|
99 | |
89 | |
52 | |
48 | |
46 |