Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
bml123
Post Patron
Post Patron

Create table with measures from a different table as of the selected date in the slicer

Hi,

 

I want to create a table with measures from a different table as of the selected date in the slicer.

I want to create a table like this where the values in target and percentage are in a different table as measures..  I want the figures to be updated based on the date value selected in the slicer. How do I achieve that?

CategoryTargetPercentage
WeekWeek Target measureWeek % measure
MTDMTD Target measureMTD % measure
YTDYTD Target measureYTD % measure

 

My sample data in Table 1

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

DateWeek Target measureWeek % measureMTD Target measureMTD % measureYTD Target measureYTD % measure
01/10/20211000.97000.510000.75
15/11/2021500.42000.415000.6
04/12/2021600.32500.350000.9

 

and I want to create a new table as shown in the first table for any date value selected in the slicer.

When 01/10/2021 is selected in the slicer, new table should show data as below

CategoryTargetPercentage
Week1000.9
MTD7000.5
YTD10000.75
1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @bml123 ,

According to your description, here's my solution.

1. Create a new table.

Table 2 =
UNION (
    ROW ( "Category", "Week" ),
    ROW ( "Category", "MTD" ),
    ROW ( "Category", "YTD" )
)

vkalyjmsft_1-1646274940611.png

2. As the calculated column can't change value due to filter, so here should create two measures.

Target =
SWITCH (
    MAX ( 'Table 2'[Category] ),
    "Week",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[Week Target measure]
        ),
    "MTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[MTD Target measure]
        ),
    "YTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[YTD Target measure]
        )
)
Percentage =
SWITCH (
    MAX ( 'Table 2'[Category] ),
    "Week",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[Week %measure]
        ),
    "MTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[MTD%measure]
        ),
    "YTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[YTD % measure]
        )
)

Get the expected result.

vkalyjmsft_2-1646275177979.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

Hi @bml123 ,

According to your description, here's my solution.

1. Create a new table.

Table 2 =
UNION (
    ROW ( "Category", "Week" ),
    ROW ( "Category", "MTD" ),
    ROW ( "Category", "YTD" )
)

vkalyjmsft_1-1646274940611.png

2. As the calculated column can't change value due to filter, so here should create two measures.

Target =
SWITCH (
    MAX ( 'Table 2'[Category] ),
    "Week",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[Week Target measure]
        ),
    "MTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[MTD Target measure]
        ),
    "YTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[YTD Target measure]
        )
)
Percentage =
SWITCH (
    MAX ( 'Table 2'[Category] ),
    "Week",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[Week %measure]
        ),
    "MTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[MTD%measure]
        ),
    "YTD",
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] ) ),
            'Table'[YTD % measure]
        )
)

Get the expected result.

vkalyjmsft_2-1646275177979.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

Hi @v-yanjiang-msft 

 

It worked perfectly fine.  Thank you very much

amitchandak
Super User
Super User

@bml123 , if your source table is like that, you can try this power query code. use it blank query and check the steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY3BDcAgDAN3yRs1dkpAnQWx/xrQBsrHOsc6pTUBlVCDUZIQmInrmVkXe9yjVJeemtCV3JLHlGcaDtNXKZ+CrLStlFjuV/HDjv9/7wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Week Target measure" = _t, #"Week % measure" = _t, #"MTD Target measure" = _t, #"MTD % measure" = _t, #"YTD Target measure" = _t, #"YTD % measure" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Week Target measure", Int64.Type}, {"Week % measure", type number}, {"MTD Target measure", Int64.Type}, {"MTD % measure", type number}, {"YTD Target measure", Int64.Type}, {"YTD % measure", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","%","Percent",Replacer.ReplaceText,{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"
amitchandak
Super User
Super User

@bml123
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

but calculation group can help refer this

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display: https://youtu.be/qMNv67P8Go0

Hi @amitchandak 

 

I have posted sample data and the expected output above.  Please can you let me know how to achieve that? Thank you.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.