Creating a table as a cross product from 2 sources

[andiart]

Hi,

I havbe a list of answers on questions for each year as follows:

year    | Q1 | Q2 | Q3 ...

-----------------------

2017   | A1 | A2 | A3 ...

2017 ...

2018   | B1 | B2 | B3 ...

2018 ...

....

Now I want a table with a) the distict years and each of this years a couple of times with each of the values Z1, Z2 and Z3 (which in real are countrows on the filtered table from the beginning and in my case these are questions as text)

year    |  Question  |  Countrows

-----------------------------------

2017   |   Z1            | 50

2017   |   Z2            | 55

2017   |   Z3            | 23

2018   |   Z1            | 49

2018   |   Z2 ....

....

How can i write the dax query for this calculated column? I need to know, how to make the cross product - the details with countrows e.g. I can then apply on the formula myself

Thanks

Andreas

[andiart · Accepted solution]

I found the answer myself, with help of chatGPT:

Grundfragen =

ADDCOLUMNS (

    CROSSJOIN (

        DISTINCT ( 'Übersicht'[Jahr] ),

        { "Waren zufrieden", "Finden die Arbeit interessant" }

    ),

    "Anzahl",

    SWITCH (

        [Value],

        "Waren zufrieden", CALCULATE ( COUNTROWS ( 'Übersicht' ), 'Übersicht'[Jahr] = EARLIER ( 'Übersicht'[Jahr] ), 'Übersicht'[Bist du mit deiner Arbeit zufrieden?] = "Ja" ) * 100 / COUNTROWS('Übersicht'),

        "Finden die Arbeit interessant", CALCULATE ( COUNTROWS ( 'Übersicht' ), 'Übersicht'[Jahr] = EARLIER ( 'Übersicht'[Jahr] ), 'Übersicht'[Finden Sie Ihre Arbeit interessant?] = "Ja" ) * 100 / COUNTROWS('Übersicht')

    )

)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...