CODEWITHSUNDEEP
powerbidax
Jim Grant
Jim Grant

Reputation: 1148

Building a table from an existing table

I have a SQL query which generates a table which in turn populates a custom visual, which works as intended. However, I now want to use this table, to avoid calling another SQL script, to populate a new table.

The original SQL creates a table such as this:

enter image description here

Now i want to use this information in order to populate another table, which if I were using the original SQL query as the starting point, I would write:

SELECT
    2 AS 'Level',
    'Warning' AS 'Text',
    [Heading] AS 'Department'
FROM
    #t
WHERE
    [Inventory Days] > 4

UNION ALL

SELECT
    1,
    'CodeRedAlert',
    [Heading]
FROM
    #t
WHERE
    [Capacity Warning] > 3

Which would output the following:

Level   Text            Department
2       Warning         Section 1
2       Warning         Section 2
2       Warning         Section 3
1       CodeRedAlert    Section 2

Which could be then used to populate the table visual within Power BI which would have icons for the warnings and code red alert.

Whilst this is achievable within SQL, considering I have the data in a table within Power BI, is there a way of building this new table within the confines of Power BI using DAX?

Cheers for any help provided.

Upvotes: 1

Views: 91

Answers (1)

Ryan B.
Ryan B.

Reputation: 3665

I think you can get to where you want to be with something like this:

Table = UNION(
    SELECTCOLUMNS(
        FILTER(Table1, [Inventory Days] > 4)
        , "Level", 2
        , "Text", "Warning"
        , "Department", 'Table1'[Heading]
    ) 
    , SELECTCOLUMNS(
        FILTER(Table1, [Inventory Days] > 3)
        , "Level", 1
        , "Text", "CodeRedAlert"
        , "Department", 'Table1'[Heading]
    )
)

This is using the SelectColumns function to pull data out of the original table and set the constant fields. I've replaced the 'where' clause from the SQL example with a Filter. Then the two different sets are stitched together with Union.

Hope it helps

Upvotes: 1

Related Questions