Transforming Multiple Table Visuals into One

[TimmK]

I have 3 individual table visuals with 21 very specific measures in total based on individual conditions for each one. I want to transform these 3 tables into one, as seen below. I can do so by creating a calculated table using the UNION and ROW functions. However, then the table stops reacting to slicers in the report. Is there another possibility where the slicers still affect the table?

[v-xuxinyi-msft]

Hi @TimmK 

Based on your description, is that your measures value in your screenshot? In order to help you better, here is the information I need to know: are you looking to dynamically change the value of your measures via slicer? If so, you can't dynamically change the value of the measures according to the slicer after you put it into the calculated table. My suggestion for this case is to merge the original table first and then create measures.

If you are still having problems, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TimmK]

Each column in each table visual is a distinct, individual measure based on specific conditions. Yes, I would like that the measures dynamically change depending on the slicer. There is only one original table in the report, namely table "Job" that I use for the table visuals and measures.

The calculated table has the following code:

Tabelle =
UNION (
    ROW (
        "Art", "Warten",
        "Sort", 1,
        "Bespannung", [W01 Bespannung],
        "Fräse SMD", [W02 Fräse SMD],
        "Fräse SEMI", [W03 Fräse SEMI],
        "Laser SMD", [W04 Laser SMD],
        "Laser SEMI", [W05 Laser SEMI],
        "Werkstatt", [W06 Werkstatt],
        "Versand", [W07 Versand],
        "SUM", [W08 SUM]
    ),
    ROW (
        "Art", "Backlog",
        "Sort", 2,
        "Bespannung", [B01 Bespannung],
        "Fräse SMD", [B02 Fräse SMD],
        "Fräse SEMI", [B03 Fräse SEMI],
        "Laser SMD", [B04 Laser SMD],
        "Laser SEMI", [B05 Laser SEMI],
        "Werkstatt", [B06 Werkstatt],
        "Versand", [B07 Versand],
        "SUM", [B08 SUM]
    ),
    ROW (
        "Art", "Aktiv",
        "Sort", 3,
        "Bespannung", [A01 Bespannung],
        "Fräse SMD", [A02 Fräse SMD],
        "Fräse SEMI", [A03 Fräse SEMI],
        "Laser SMD", [A04 Laser SMD],
        "Laser SEMI", [A05 Laser SEMI],
        "Werkstatt", [A06 Werkstatt],
        "Versand", [A07 Versand],
        "SUM", [A08 SUM]
    )
)

Thus, it is just for organizing my measures in the intended rows and columns. But then, as mentioned before, it does not dynamically react based on the slicers anymore.

Here is sample data, it is just table "Job":

Here are the measures I use:

M01 Stencil = DISTINCTCOUNT(Job[Order CKey])
A02 Fräse SMD = 
CALCULATE(
    [M01 Stencil],
    LEFT(Job[Station Key], 2) = "ST" &&
    LEN(Job[Station Key]) >= 3 &&
    NOT(ISERROR(VALUE(RIGHT(Job[Station Key], LEN(Job[Station Key]) - 2)))) &&
    NOT(ISERROR(VALUE(RIGHT(Job[Last Station Key], LEN(Job[Last Station Key]) - 2)))) &&
    Job[Station Key] = Job[Last Station Key] &&
    ISBLANK(Job[End]) &&
    Job[Status] = "Aktiv" &&
    Job[Market] <> "SEMI" &&
    Job[Bereich] = "Fräse"
)
A03 Fräse SEMI = 
CALCULATE(
    [M01 Stencil],
    LEFT(Job[Station Key], 2) = "ST" &&
    LEN(Job[Station Key]) >= 3 &&
    NOT(ISERROR(VALUE(RIGHT(Job[Station Key], LEN(Job[Station Key]) - 2)))) &&
    NOT(ISERROR(VALUE(RIGHT(Job[Last Station Key], LEN(Job[Last Station Key]) - 2)))) &&
    Job[Station Key] = Job[Last Station Key] &&
    ISBLANK(Job[End]) &&
    Job[Status] = "Aktiv" &&
    Job[Market] = "SEMI" &&
    Job[Bereich] = "Fräse"
)
A04 Laser SMD = 
CALCULATE(
    [M01 Stencil],
    ((LEFT(Job[Station Key], 2) = "LA" || LEFT(Job[Station Key], 1) = "S") &&
    LEN(Job[Station Key]) >= 3) &&
    ((LEFT(Job[Last Station Key], 2) = "LA" || LEFT(Job[Last Station Key], 1) = "S") &&
    LEN(Job[Last Station Key]) >= 3) &&
    Job[Station Key] = Job[Last Station Key] &&
    ISBLANK(Job[End]) &&
    Job[Status] = "Aktiv" &&
    Job[Market] <> "SEMI" &&
    Job[Bereich] = "Laser"
)
A05 Laser SEMI = 
CALCULATE(
    [M01 Stencil],
    ((LEFT(Job[Station Key], 2) = "LA" || LEFT(Job[Station Key], 1) = "S") &&
    LEN(Job[Station Key]) >= 3) &&
    ((LEFT(Job[Last Station Key], 2) = "LA" || LEFT(Job[Last Station Key], 1) = "S") &&
    LEN(Job[Last Station Key]) >= 3) &&
    Job[Station Key] = Job[Last Station Key] &&
    ISBLANK(Job[End]) &&
    Job[Status] = "Aktiv" &&
    Job[Market] = "SEMI" &&
    Job[Bereich] = "Laser"
)
A06 Werkstatt = 
CALCULATE(
    [M01 Stencil],
    (Job[Station Key] IN {"FI", "KS", "EP", "SK", "SW", "KL", "RR"} ||
    (LEFT(Job[Station Key], 2) = "PL" && LEN(Job[Station Key]) >= 3) ||
    (LEFT(Job[Station Key], 2) = "LB" && LEN(Job[Station Key]) >= 3)) &&
    (Job[Last Station Key] IN {"FI", "KS", "EP", "SK", "SW", "KL", "RR"} ||
    (LEFT(Job[Last Station Key], 2) = "PL" && LEN(Job[Last Station Key]) >= 3) ||
    (LEFT(Job[Last Station Key], 2) = "LB" && LEN(Job[Last Station Key]) >= 3)) &&
    Job[Station Key] = Job[Last Station Key] &&
    ISBLANK(Job[End]) &&
    Job[Status] = "Aktiv" &&
    Job[Bereich] = "Werkstatt"
)
A07 Versand = 
A08 SUM = [A01 Bespannung]+[A02 Fräse SMD]+[A03 Fräse SEMI]+[A04 Laser SMD]+[A05 Laser SEMI]+[A06 Werkstatt]+[A07 Versand] 
B02 Fräse SMD = 
CALCULATE(
[M01 Stencil],
Job[Station Key] = "ST" &&
Job[Last Station Key] = "ST" &&
ISBLANK(Job[End]) &&
Job[Status] = "Aktiv" &&
Job[Market] <> "SEMI"
)
B03 Fräse SEMI = 
CALCULATE(
[M01 Stencil],
Job[Station Key] = "ST" &&
Job[Last Station Key] = "ST" &&
ISBLANK(Job[End]) &&
Job[Status] = "Aktiv" &&
Job[Market] = "SEMI"
)
B04 Laser SMD = 
CALCULATE(
[M01 Stencil],
Job[Station Key] = "LA" &&
Job[Last Station Key] = "LA" &&
ISBLANK(Job[End]) &&
Job[Status] = "Aktiv" &&
Job[Market] <> "SEMI"
)
B05 Laser SEMI = 
CALCULATE(
[M01 Stencil],
Job[Station Key] = "LA" &&
Job[Last Station Key] = "LA" &&
ISBLANK(Job[End]) &&
Job[Status] = "Aktiv" &&
Job[Market] = "SEMI"
)
B06 Werkstatt = 
CALCULATE(
[M01 Stencil],
Job[Station Key] = "W1" &&
Job[Last Station Key] = "W1" &&
ISBLANK(Job[End]) &&
Job[Status] = "Aktiv"
)
B07 Versand = 
B08 SUM = [B01 Bespannung]+[B02 Fräse SMD]+[B03 Fräse SEMI]+[B04 Laser SMD]+[B05 Laser SEMI]+[B06 Werkstatt]+[B07 Versand]
W02 Fräse SMD = 
CALCULATE(
[M01 Stencil],
Job[Bereich]="Bespannung" &&
Job[Market]<>"SEMI" &&
Job[Status]="Aktiv"
)
W03 Fräse SEMI = 
CALCULATE(
[M01 Stencil],
Job[Bereich]="Bespannung" &&
Job[Market]="SEMI" &&
Job[Status]="Aktiv"
)
W04 Laser SMD = 
CALCULATE(
    [M01 Stencil],
    Job[Last Station Key]="ST" &&
    Job[Last Station Key]=Job[Station Key] &&
    Job[End]=BLANK() &&
    Job[Market]<>"SEMI" &&
    Job[Status]="Aktiv"
)
W05 Laser SEMI = 
CALCULATE(
    [M01 Stencil],
    Job[Last Station Key]="ST" &&
    Job[Last Station Key]=Job[Station Key] &&
    Job[End]=BLANK() &&
    Job[Market]="SEMI" &&
    Job[Status]="Aktiv"
)
W06 Werkstatt = 
CALCULATE(
    [M01 Stencil],
    Job[Station Key]="LA" &&
    Job[End]=BLANK() &&
    Job[Bereich]="Laser" &&
    Job[Status]="Aktiv"
)
W07 Versand = 
CALCULATE(
    [M01 Stencil],
    'Job'[Station Key] = "W1" &&
    NOT(ISBLANK('Job'[End])) &&
    Job[Bereich]="Werkstatt" &&
    Job[Status]="Aktiv"
)
W08 SUM = [W01 Bespannung]+[W02 Fräse SMD]+[W03 Fräse SEMI]+[W04 Laser SMD]+[W05 Laser SEMI]+[W06 Werkstatt]+[W07 Versand]