Duplicate the values in a matrix without entering data in the excel sheet

[Anonymous]

Hi All,

I am currently stuck in this scenario. I have a matrix table for A-G categories. There is conditional formating on the Num values. If for that Date the Num value is lowest then it's green and if the value is highest then it's red. The problem is there are some dates in the data which has value for just one or two categories( coz there are some other columns whose values have changed for that particular category).  Forex- 26 Feb 2020, some other column value has changed for G while there was no change for rest of the categories on that day, hence there is a data entry for G with 7.1 which is green. Ideally, it should be red as it's the highest of all categories. Is there a way in power bi in which I can use the previous date value if the data is not there. I don't want to add a row in the source excel sheet.
For ex- 26 feb, A should have value as 6.0, B-6.1, C-6.5, D-7.1, E- 6.8, F-5.9 (Use previous date value- 05 Feb 2020)

I have searched in the community forum as well as googled it but not able to find a solution.

.

Any help would be greatly appreciated.

Thank you.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

Please follow the below steps to achieve it:

1. Create a category table

Create category table

2. Create a measure with below formula and drag the fields Categories[Categories], Table[Date] and Measure onto Matrix( Rows:Table[Date]     Columns: Categories[Categories]      Values: Measure)

Measure = 
var _curdate=MAX('Table'[Date])
var _predate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<_curdate&&'Table'[Categories]=MAX('Categories'[Categories])&&NOT(ISBLANK('Table'[Num]))))
var _prenum=CALCULATE(MAX('Table'[Num]),FILTER(ALL('Table'),'Table'[Date]=_predate&&'Table'[Categories]=MAX('Categories'[Categories])))
var _curnum=CALCULATE(MAX('Table'[Num]),FILTER(ALL('Table'),'Table'[Date]=_curdate&&'Table'[Categories]=MAX('Categories'[Categories])))
return if(ISBLANK(_curnum),_prenum,_curnum)

Create a measure I created a sample pbix file with above steps, you can get it from this link.

Best Regards

Rena

[edhans]

It would help if you would give a screenshot of your conditional formatting screen. You didn't say how you were determining min/max. A measure, a summarization as below, etc?

But you can change the order of the application of the formats with the up/down arrows on the right. If it is a measure, you could rewrite the measure to say no matter what, if there is only one value, it is a MIN value, not MAX.  We need details.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Hi @edhans ,

Apologies for inconvinience. Here is the screen shot and DAX measure that I used for conditional formatting.

TMinMax = SUM(Sheet1[Num])

TMinMaxUnit = VAR MinVal =  CALCULATE(MIN(Sheet1[Num]),ALLEXCEPT(Sheet1,Sheet1[Date])) VAR MaxVal = CALCULATE(MAX(Sheet1[Num]),ALLEXCEPT(Sheet1,Sheet1[Date])) VAR CurrentVal = [TMinMax] VAR Result =  SWITCH(TRUE(), CurrentVal = MinVal,2, CurrentVal = MaxVal,1) RETURN Result

[edhans]

Seems to me you can add a condition to your SWITCH so that if the MinVal = MaxVal (therefore only one value), then return 2, which is what you want for your MinVal.

TMinMaxUnit =
VAR MinVal =
    CALCULATE(
        MIN( Sheet1[Num] ),
        ALLEXCEPT(
            Sheet1,
            Sheet1[Date]
        )
    )
VAR MaxVal =
    CALCULATE(
        MAX( Sheet1[Num] ),
        ALLEXCEPT(
            Sheet1,
            Sheet1[Date]
        )
    )
VAR CurrentVal = [TMinMax]
VAR Result =
    SWITCH(
        TRUE(),
        MinVal = MaxVal, 2,     <--- New condition to check for in SWITCH
        CurrentVal = MinVal, 2,
        CurrentVal = MaxVal, 1
    )
RETURN
    Result

[Anonymous]

Hi @edhans,

Thank you for your reply. But my requirement is different. I want to use previous values for the categories if there is no entry in the source table. 

For ex- 26 feb 2020, A should have value as 6.0, B-6.1, C-6.5, D-7.1, E- 6.8, F-5.9 (Use previous date value- 05 Feb 2020)

[edhans]

Without data this is hard to do, but something along the lines of:

Current or Previous Value =
VAR varCurrentDate = Table[Date]
VAR varCurrentValue =
    MAX( Table[Value] )
VAR varCurrentCategory =
    MAX( Table[Category] )
VAR varPreviousValue =
    MAXX(
        FILTER(
            ALL( Table ),
            Table[Category] = varCurrentCategory
                && Table[Date] < varCurrentDate
        ),
        Table[Value]
    )
RETURN
    IF(
        ISBLANK( varCurrentValue ),
        varPreviousValue,
        varCurrentValue
    )

If you need more help, post actual (sample) data, and expected results.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Hi @edhans ,

Here is the sample data-

Date	Categories	Num	RC%
03/01/2020	A	6.016	14
03/01/2020	B	6.097	5.5
03/01/2020	C	6.540	10
03/01/2020	D	7.090	15
03/01/2020	E	6.780	30
03/01/2020	F	5.905	16
03/01/2020	G	7.119	30
05/02/2020	C	6.540	10
05/02/2020	E	6.780	30
05/02/2020	F	5.905	16
05/02/2020	A	6.016	14
05/02/2020	B	6.097	5.5
05/02/2020	D	7.090	15
05/02/2020	G	7.119	20
26/02/2020	G	7.119	28
02/03/2020	C	6.540	10
02/03/2020	E	6.780	30
02/03/2020	B	6.097	5.5
02/03/2020	A	6.016	14
02/03/2020	F	5.905	12
02/03/2020	D	7.090	15
01/04/2020	C	6.540	10
01/04/2020	E	6.780	30
01/04/2020	F	5.905	12
01/04/2020	B	5.391	5.5
01/04/2020	D	7.090	15
01/04/2020	G	7.119	31
06/04/2020	G	7.119	20
09/04/2020	A	6.016	23
21/04/2020	G	7.119	25
03/05/2020	A	5.618	23
03/05/2020	B	5.391	5.5
03/05/2020	C	5.79	10
03/05/2020	D	7.090	15
03/05/2020	G	7.119	25
03/05/2020	E	6.78	30
03/05/2020	F	5.905	12
03/06/2020	A	5.618	23
03/06/2020	B	5.391	5.5
03/06/2020	C	5.79	10
03/06/2020	D	6.24	15
03/06/2020	G	7.119	25
03/06/2020	E	6.78	30
03/06/2020	F	5.905	12
08/06/2020	A	5.618	23
08/06/2020	B	5.391	5.5
08/06/2020	C	5.79	10
08/06/2020	D	6.24	15
08/06/2020	G	7.119	25
08/06/2020	E	6.78	30
08/06/2020	F	5.905	12
01/07/2020	A	5.618	26
01/07/2020	B	5.391	5.5
01/07/2020	C	5.79	10
01/07/2020	D	6.24	15
01/07/2020	G	7.119	25
01/07/2020	E	6.78	30
01/07/2020	F	5.905	12

Current matrix-

Expected Matrix

highlighted in yellow

Thank you.

[edhans · Accepted solution]

Hi @Anonymous - I tinkered with this yesterday and got not very far until I realized what was happening. When I tried to get the current date and category for Feb 26, category A, I was getting nothing. After messing around in DAX studio I figured out it was just because that "cell" existed in the matrix, it didn't exist in the data. So I did a little data modeling in Power Query. The below M code does this:

1. Finds all "missing categories" for each date, assuming if one category exists for a date, all six must exist.
2. Creates a table based on the date and missing category
3. Appends that to the original table. The values will be NULL, which is what we want.
4. The step that does that is the #"Added Missing Categories" step. Everything else is basic UI transformations.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNBbsQgDEXvkvUoYAjGLDszbQ8xmvtfo4S0yg8Yq4sIKU8/9jPx67VERy744Jfb8lEfXj1xPWlb3rcLvR+05HqmNfX40XDa9qzv4bM+uWZ3OCQ/WzJLPeKQ/GrVik97lHv63b5LVM5scmHeEsCxKkClKlBlTkC1OQEeRwEQfcLRVWDndS4Hd3HuC3D0Baj1DFgRBnoZVujp6Etum3cMcOwYoFIU6L3RWAh8AJs9Xf4papR1+ntD5aQ4qBCPUZDzevpvERKm08okZxqo5gT40V7ngvuXrP1L/2tK285k3EJ0bPqw7cOWD6MPr2FDHzZ92PJhw0dMH7F9xPIRy0dMH7F8xPQhl1Uf7qm+Q3nuA3D0Aaj4ANW2Ps983j8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t, Num = _t, #"RC%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Categories", type text}, {"Num", type number}, {"RC%", type number}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-BS"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Date"}, {{"All Rows", each _, type table [Date=date, Categories=text, Num=number, #"RC%"=number]}}),
    #"Added Missing Categories" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Missing Categories", 
            each 
            let 
                varCurrentDate = [Date]
            in
            Table.Combine({
                Table.AddColumn(
                    Table.FromList(
                        List.Difference(
                            {"A".."G"},
                            [All Rows][Categories]
                            ),
                        Splitter.SplitByNothing(),
                        {"Categories"}, 
                        null, 
                        ExtraValues.Error
                        ),
                    "Date",
                    each varCurrentDate
            ),[All Rows]})
            ),
    #"Expanded Missing Categories" = Table.ExpandTableColumn(#"Added Missing Categories", "Missing Categories", {"Categories", "Num", "RC%"}, {"Categories", "Num", "RC%"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Missing Categories",{"Date", "Categories", "Num", "RC%"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Categories", type text}, {"Num", type number}, {"RC%", type number}})
            
            
in
    #"Changed Type1"

So that returns a table that looks like this for Feb 26 as an example:

Then it was a simple matter of applying the basic pattern above, which in detail is this measure:

Total Num =
VAR varCurrentDate =
    MAX( 'Table'[Date] )
VAR varCurrentCategory =
    MAX( 'Table'[Categories] )
VAR varCurrentValue =
    SUM( 'Table'[Num] )
VAR varPreviousDate =
    MAXX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Num]
                <> BLANK()
                && 'Table'[Date] < varCurrentDate
                && 'Table'[Categories] = varCurrentCategory
        ),
        'Table'[Date]
    )
VAR varPreviousValue =
    SUMX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Date] = varPreviousDate
                && 'Table'[Categories] = varCurrentCategory
        ),
        'Table'[Num]
    )
VAR Result =
    COALESCE(
        varCurrentValue,
        varPreviousValue
    )
RETURN
    Result

That returns this matrix:

To use the M code above in your model, do the following:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

5) See this article if you need help using this M code in your model.

And here is the PBIX file so you can see all of the work.

[Anonymous]

Hi @edhans ,

Thank you for solution. I had some doubts, when I try the m-code with Categories as A,B,C ,D,E,F,G it works perfectly. But what if my Categories are like name of companies like ex- Apple, Blackberry , Casio, Huawei, Honor, Sony, Samsung a and not just like A-G. 

I guess the changes needs to be done in 

List.Difference(
{"A".."G"},
[All Rows][Categories]
)

I tried but I am not geting how to get around it. Would be great if you could help me in this.

#"Added Missing Categories" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Missing Categories", 
            each 
            let 
                varCurrentDate = [Date]
            in
            Table.Combine({
                Table.AddColumn(
                    Table.FromList(
                        List.Difference(
                            {"A".."G"},
                            [All Rows][Categories]
                            ),
                        Splitter.SplitByNothing(),
                        {"Categories"}, 
                        null, 
                        ExtraValues.Error
                        ),
                    "Date",
                    each varCurrentDate
            ),[All Rows]})
            ),

Best regards,

Supriya

[Anonymous]

Hi @edhans ,

I was able to solve it. I just had r=to specify all the names of the companies in "" commas in List.Difference.

Ex-{

List.Difference(
{"Apple", "Blackberry" , "Casio", "Huawei", "Honor", "Sony, Samsung"},
[All Rows][Categories]
)

But now I am stuck in the measure that is being used. The table that I am working on is having more column and there is a column named Region that has 2 values Rural and Urban. Without any slicer it is working perfectly but if I use the slicer with Region column, I am not getting the desired result. I tried to use ALLSELECTED, ALLEXCEPT  instead of ALL('Table ')but didn't find a solution. I am missing something. Could you help me on this.

Also, I have a older version of Power BI- September 2019 and it doesn't have COALESCE function so I just used Switch statement below-

VAR Result =
SWITCH(
FALSE(),
ISBLANK(varCurrentUnitRate),varCurrentUnitRate
,ISBLANK(varPreviousValue),varPreviousValue)
)

Measure-

Total Num =
VAR varCurrentDate =
    MAX( 'Table'[Date] )
VAR varCurrentCategory =
    MAX( 'Table'[Categories] )
VAR varCurrentValue =
    SUM( 'Table'[Num] )
VAR varPreviousDate =
    MAXX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Num]
                <> BLANK()
                && 'Table'[Date] < varCurrentDate
                && 'Table'[Categories] = varCurrentCategory
        ),
        'Table'[Date]
    )
VAR varPreviousValue =
    SUMX(
        FILTER(
            ALL( 'Table' ),
            'Table'[Date] = varPreviousDate
                && 'Table'[Categories] = varCurrentCategory
        ),
        'Table'[Num]
    )
VAR Result =
    COALESCE(
        varCurrentValue,
        varPreviousValue
    )
RETURN
    Result

Regards,

Supriya

[edhans]

I would start a new thread so we can separate the M from the DAX issue. You are going to have to give more info. Give us the model that has the Region column and how it relates. Also, what do you mean it isn't giving you the desired result? What is it giving? It may be a simple as adding a filter for the region (removing more likely).

I would also upgrade to the latest available desktop. It is July 2020 unless you are on Report Server, in which case it is June 2020. So many new features and bugs fixed in the last 10 months!

[Anonymous]

Hi @edhans,

Thank you very much for your reply. I have started a new thread for the DAX measure and tried to give all the details that would be needed. Here is the link to the post- 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-to-copy-the-the-values-of-previous-da... 

I am currently stuck on this problem.  It would be great if you could help me in this. 

Thank you.

Best Regards,

Supriya

[edhans]

Two ways @Anonymous 

1. Hardcode your list like this - {"Apple", "Blueberry", "Grape"} - use that to replace my {"A".."G"} list.
2. Make your list dynamic and refer to it in my original query. Say, for example, your list would come from a list of products sold.
   
   1. Create a reference to the sales list of product sold. - right-click, Reference
   2. Right-Click on the product list column, Remove Other Column
   3. Right-Click on the product list column, Remove Duplicates
   4. Right-Click on the product list column, Drill Down
   5. Now your products are in a list. Call that list "Fruits" (or whatever)
   6. In my query replace {"A".."G"} with Fruits. That will be a pointer to that new list you just created.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

Please follow the below steps to achieve it:

1. Create a category table

Create category table

2. Create a measure with below formula and drag the fields Categories[Categories], Table[Date] and Measure onto Matrix( Rows:Table[Date]     Columns: Categories[Categories]      Values: Measure)

Measure = 
var _curdate=MAX('Table'[Date])
var _predate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<_curdate&&'Table'[Categories]=MAX('Categories'[Categories])&&NOT(ISBLANK('Table'[Num]))))
var _prenum=CALCULATE(MAX('Table'[Num]),FILTER(ALL('Table'),'Table'[Date]=_predate&&'Table'[Categories]=MAX('Categories'[Categories])))
var _curnum=CALCULATE(MAX('Table'[Num]),FILTER(ALL('Table'),'Table'[Date]=_curdate&&'Table'[Categories]=MAX('Categories'[Categories])))
return if(ISBLANK(_curnum),_prenum,_curnum)

Create a measure I created a sample pbix file with above steps, you can get it from this link.

Best Regards

Rena