Extracting specific rows into columns of new table

[Idkpowerbi]

This table as shown in the image is sorted datewise with multiple columns.I need to extract the columns ASH,CSR,CRI,FC 
for only last the two dates and insert into two a new table with three columns"parameter name", "today's value" and
 "yesterday's value". 

 

[tamerj1 · Accepted solution]

@Idkpowerbi 
well, it's not easy to work with such complex queries without data to test on. Thus despite positive results have a very good chance but still not 100% sure. Please test from your end.

New Table =
VAR T1 =
    TOPN ( 2, 'Table', 'Table'[Date] )
VAR T2 =
    SELECTCOLUMNS (
        T1,
        "@Day", IF ( [Date] = MAXX ( T, [Date] ), "Today", "Yesterday" ),
        "@Name", "ASH|CSR|CRI|FC",
        "@Value",
            [ASH] & "|" & [CSR] & "|" & [CRI] & "|" & [FC]
    )
VAR T3 =
    FILTER ( T2, [@Day] = "Today" )
VAR T4 =
    FILTER ( T2, [@Day] = "Yesterday" )
RETURN
    SELECTCOLUMNS (
        { 1, 2, 3, 4 },
        "Parameter Name", MAXX ( T3, PATHITEM ( [@Name], [Value] ) ),
        "Today's Value", MAXX ( T3, PATHITEM ( [@Value], [Value] ) ),
        "Yesterday's Value", MAXX ( T4, PATHITEM ( [@Value], [Value] ) )
    )

[Idkpowerbi]

@tamerj1 A small upgrade to this problem. Consider the above table is for factory 1.I have another table for factory 2, with same parameters. Can you extend the code to include these parameters as well. Also another column with values either factory1 or factory2 to their corresponding parameters which I can use for filtering. 

[tamerj1]

@Idkpowerbi 

Please provide some screenshots. Also it would be great to provide a sample data along with expected results 

[Idkpowerbi]

This is the final table I desire. The input table will have the same format as before. Here I give you the tables with just two parameters and last three dates. It has more than two parameters and sorted datewise. So Today's value will be of the last date and yesterday's value is the last before date(same as before)

Factory 2:

Date	Gross Production2	Prime Production2
2/8/2023	7468	7277
2/9/2023	7474	7287
2/10/2022	7473	7286

Factory 1:

Date	Gross Production	Prime production
2/8/2023	2328	2244
2/9/2023	2326	2268
2/10/2023	2313	2255

[tamerj1 · Accepted solution]

@Idkpowerbi 
Please refer to attached sample file with the proposed solution

New Table = 
VAR T1 =
    TOPN ( 2, 'Factory 1', 'Factory 1'[Date] )
VAR T2 =
    SELECTCOLUMNS (
        T1,
        "@Day", IF ( [Date] = MAXX ( T1, [Date] ), "Today", "Yesterday" ),
        "@Name", "	Gross Production|Prime Production",
        "@Value",
            [Gross Production] & "|" & [Prime production] 
    )
VAR T3 =
    FILTER ( T2, [@Day] = "Today" )
VAR T4 =
    FILTER ( T2, [@Day] = "Yesterday" )
VAR T5 =
    SELECTCOLUMNS (
        { 1, 2 },
        "Parameter Name", MAXX ( T3, PATHITEM ( [@Name], [Value] ) ),
        "Today's Value", MAXX ( T3, PATHITEM ( [@Value], [Value] ) ),
        "Yesterday's Value", MAXX ( T4, PATHITEM ( [@Value], [Value] ) ),
        "Factory", 1
    )
VAR T6 =
    TOPN ( 2, 'Factory 2', 'Factory 2'[Date] )
VAR T7 =
    SELECTCOLUMNS (
        T6,
        "@Day", IF ( [Date] = MAXX ( T6, [Date] ), "Today", "Yesterday" ),
        "@Name", "	Gross Production|Prime Production",
        "@Value",
            [Gross Production2] & "|" & [Prime production2] 
    )
VAR T8 =
    FILTER ( T7, [@Day] = "Today" )
VAR T9 =
    FILTER ( T7, [@Day] = "Yesterday" )
VAR T10 =
    SELECTCOLUMNS (
        { 1, 2 },
        "Parameter Name", MAXX ( T8, PATHITEM ( [@Name], [Value] ) ),
        "Today's Value", MAXX ( T8, PATHITEM ( [@Value], [Value] ) ),
        "Yesterday's Value", MAXX ( T9, PATHITEM ( [@Value], [Value] ) ),
        "Factory", 2
    )
RETURN
    UNION ( T5, T10 )

[Idkpowerbi]

Works great.

P. S. You are awesome dude. 

[tamerj1]

Hi @Idkpowerbi 
Please try

New Table =
VAR T =
    TOPN ( 2, 'Table', 'Table'[Date] )
RETURN
    SELECTCOLUMNS (
        T,
        "Day", IF ( [Date] = MAXX ( T, [Date] ), "Today", "Yesterday" ),
        "ASH", [ASH],
        "CSR", [CSR],
        "CRI", [CRI],
        "FC", [FC]
    )

[Idkpowerbi]

 It works as intended and I can see the newtable is there but Can I have it column wise? I intend to have three columns, 

parameter name,today's value,Yesterday's value. I am super new to DAX so please help me out.

[tamerj1 · Accepted solution]

@Idkpowerbi 
well, it's not easy to work with such complex queries without data to test on. Thus despite positive results have a very good chance but still not 100% sure. Please test from your end.

New Table =
VAR T1 =
    TOPN ( 2, 'Table', 'Table'[Date] )
VAR T2 =
    SELECTCOLUMNS (
        T1,
        "@Day", IF ( [Date] = MAXX ( T, [Date] ), "Today", "Yesterday" ),
        "@Name", "ASH|CSR|CRI|FC",
        "@Value",
            [ASH] & "|" & [CSR] & "|" & [CRI] & "|" & [FC]
    )
VAR T3 =
    FILTER ( T2, [@Day] = "Today" )
VAR T4 =
    FILTER ( T2, [@Day] = "Yesterday" )
RETURN
    SELECTCOLUMNS (
        { 1, 2, 3, 4 },
        "Parameter Name", MAXX ( T3, PATHITEM ( [@Name], [Value] ) ),
        "Today's Value", MAXX ( T3, PATHITEM ( [@Value], [Value] ) ),
        "Yesterday's Value", MAXX ( T4, PATHITEM ( [@Value], [Value] ) )
    )

[Idkpowerbi]

Works beautifully. Life saver.