Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello experts!,
I've found some post like this one asking for dynamic calculated column based on slicer, but let me explain what I need so you can evaluate if there is a way so solve my issue:
I need to replicate (*1) calculated column but in the SWITCH validation, use a column from a not related table, I expect something like (*2)
(*1)
PRODUCTO = SWITCH(ANTIGRIPGSK[FILTRAR A NIVEL], "CATEGORY",ANTIGRIPGSK[CATEGORIA],"FABRICANTE PHARMA",ANTIGRIPGSK[FABRICANTE],"MARCA PHARMA",ANTIGRIPGSK[MARCA],"ETAPA DE VIDA PHARMA ESPECIAL",ANTIGRIPGSK[ETAPA DE VIDA],"VARIEDAD PHARMA",ANTIGRIPGSK[VARIEDAD],"PRESENTACION PHARMA",ANTIGRIPGSK[PRESENTACION])
(*2)
PRODUCTO = SWITCH(NOT_RELATED_TABLE[FIELD], "CATEGORY",ANTIGRIPGSK[CATEGORIA],"FABRICANTE PHARMA",ANTIGRIPGSK[FABRICANTE],"MARCA PHARMA",ANTIGRIPGSK[MARCA],"ETAPA DE VIDA PHARMA ESPECIAL",ANTIGRIPGSK[ETAPA DE VIDA],"VARIEDAD PHARMA",ANTIGRIPGSK[VARIEDAD],"PRESENTACION PHARMA",ANTIGRIPGSK[PRESENTACION])
I know I cannot directly access a column form a not related table in a calculated column so I tried to use SLEECTEDVALUES(), VALUES(), FIRSTNONBLANK(), and other functions without any luck.
I really need some help on this and any idea will be much appreciated.
Hi SebastianLM,
As a general solution, you can use USERELATIONSHIP() if we don't want to build relationship between tables. If possible, could you share some sample data and expected result you want to achieve?
Regards,
Jimmy Tao
Thanks @Anonymous and @v-yuta-msft,
Quick question first, this USERELATIONSHIP() function apply he filters done in both sides? Because I don't want that, I just want to make a calculated column in one table using the unique selected item of a slicer of a not related table.
I'm trying this without success:
I created one measure called SELECTEDM in table ANTIGRIPGSK:
SELECTEDM = SELECTEDVALUE('4SLICER'[VISUAL])
I created a calculated column called VIZ in table ANTIGRIPGSK:
VIZ = SWITCH([SELECTEDM],"CATEGORY",ANTIGRIPGSK[CATEGORIA],"FABRICANTE PHARMA",ANTIGRIPGSK[FABRICANTE],"MARCA PHARMA",ANTIGRIPGSK[MARCA],"ETAPA DE VIDA PHARMA ESPECIAL",ANTIGRIPGSK[ETAPA DE VIDA],"VARIEDAD PHARMA",ANTIGRIPGSK[VARIEDAD],"PRESENTACION PHARMA",ANTIGRIPGSK[PRESENTACION],"NA")
In the screnshoots below you can see the current behaviour that I don't understand, it is always retrieving the else value of the SWITCH() function, also VIZ is not generating any error, and SELECTEDM is retreiving the value I expect when I select something in '4SLICER'[VISUAL]
Hello @SebastianLM
Unfortunately you can't do it, the values of a calculated column only shows an update on the information when you refresh the data, it doesn't changed for the selected value of a slicer.
Sorry for the bad news.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |