How to repeat the List of items for each Category using Power Query

How to repeat the List of items for each Category using M-Query in Power BI

Scenario:

Suppose we have the sample of Product details as per below. In this sample we have 10 distinct Products and 3 unique Product Segments.

From this sample, we need to repeat the list of Products for each Product Segment.

We can achieve this Scenario, using the following M-Query (Power Query):

ProdListBySegment =

let

    Source = Product_Details,

    ProdSegmentList = List.Distinct(Source[Prod_Segment]),

    ProdNameList = List.Repeat(List.Distinct({Source[Prod_Name]}), List.Count(ProdSegmentList)),

    TableFromProdLists = Table.FromColumns(ProdNameList, ProdSegmentList)

in

    TableFromProdLists

Result:

Please Note:
I used above sample only to explain how this technique works. You can apply this technique for the right Scenario.

Thanks, Tamatam

How to highlight Visual rows based on Slicer Selection in Power BI

How to highlight Visual rows based on Selected values from Slicer in Power BI

Scenario:
Suppose we have a simple data Model as follows:

The Table visual built on this Model as follows:

Now we want to highlight (NOT filtering) the rows on this table, based on the selected values from a Product Name Slicer.

We can achieve this Scenario, by creating a disconnected (NOT related to the Model) table with a list of unique Product Names as shown below:

UniqProducts = ALLNOBLANKROW(ds_Sample[Prod_Name])

Next, by using the following DAX formula, we can identify which Products are selected, and then based on that we can apply the color formatting to highlight.

Highlight Products =

VAR IsFilteredProd = ISFILTERED(UniqProducts[Prod_Name])

VAR vCountProd =

COUNTROWS(

    FILTER( VALUES(ds_Sample[Prod_Name]),

        SELECTEDVALUE(ds_Sample[Prod_Name]) IN VALUES(UniqProducts[Prod_Name])

        )

    )

VAR vFormat = IF(vCountProd>=1, "#00bfa0", BLANK())

VAR vResult = IF(IsFilteredProd=TRUE(), vFormat)

RETURN

vResult

Next, use this formula to apply the conditional formatting for the Cell elements > Series > BG Color for the required columns selected on the visual.

Finally, the result is as follows:

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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

How to calculate Max Sale Date and Product details using DAX in Power BI

How to return Max Sale Date and Product with correct Totals using DAX in Power BI
Suppose we have simple Data Modal as follows.

The sample Data from ds_Sample is as follows:

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

Scenario 1:

Returning the Max Sale Date, and the Product that contributing the Max Sales

We can achieve this Scenario using the following DAX Functions.

Net Sales = SUM(ds_Sample[Qnty_Sold])

Max Sales By Date =

MAXX(

    VALUES(ds_Sample[Order_Date]),  

    [Net Sales]

   /* CALCULATE(SUM(ds_Sample[Qnty_Sold])) */

)

Max Sale Date =

CALCULATE( FIRSTNONBLANK(ds_Sample[Order_Date],1),

    FILTER( VALUES(ds_Sample[Order_Date]),

       [Net Sales] = MAX(ds_Sample[Qnty_Sold])

    )

    )

Max Sale Prod By Date =

CALCULATE( FIRSTNONBLANK(ds_Sample[Prod_Name],1),

    FILTER( VALUES(ds_Sample[Order_Date]),

       [Net Sales] = MAX(ds_Sample[Qnty_Sold])

    )

    )

Result:

Note:
The Total value 174 is the Max Total Sales by Date not the Total of 3 Max Sales.

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

Scenario 2: 

Returning the Unique Orders and Max Order value by Segment based on Order Date, with a Correct Total.

Max Sales = MAX(ds_Sample[Qnty_Sold])

Unique Orders = DISTINCTCOUNT(ds_Sample[Order_Date])

Note:

Please note that, I have considered the [Order_Date] for the Unique Orders calculation, only to explain this Scenario.

The [Order_Date] Column is having Duplicate Records, as we can expect the multiple Orders on the same date.

Now, to fulfil this Scenario, we will fix the Total Issue for [Max Sales] and [Unique Orders] by using the Measures as shown below:

Max Sales (Total) =

SUMX( VALUES(ds_Sample[Prod_Segment]),

    [Max Sales]

    /* CALCULATE(MAX(ds_Sample[Qnty_Sold])) */

    )

Unique Orders (Total) =

SUMX( VALUES(ds_Sample[Prod_Segment]),

    [Unique Orders]

    /* CALCULATE(DISTINCTCOUNT(ds_Sample[Order_Date])) */

    )

Result:

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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