Dynamically adding rows

[Jithin00]

Hey all, i have got 4 quaters that is q1=oct,nov,dec     q2=Jan,feb,mar      q3=april,may,june     q4=july,aug,sept

(Note:- When the quater ends it should print the last 2 months data of the quater dynamically.)

So currently q1,q2,q3 is over so the table looks like this, so this is the current state of my report.

	q1	q2	q3	may	june
Innovations	5	5	5	5	5
Loyalty	4	4	4	4	4
Value	6	6	6	6	6

But when quater 4 ends it should dynamically create columns q4, aug, sept and should look like this, so this is the expected output:-

	q1	q2	q3	q4	aug	sept
Innovations	5	5	5	5	5	5
Loyaltly	4	4	4	4	4	4
Value	6	6	6	6	6	6

Requesting for help for the above problem.

Thankyou

[Anonymous · Accepted solution]

Hi,@Jithin00 

I am glad to help you.  

According to your description, you want to dynamically adding rows? 

If I understand you correctly, then you can refer to my solution.  

First of all, I need to point out that the matrix visualization in power BI itself has a lot of limitations and doesn't always exactly fit our needs. For example, you now want to be able to dynamically add new columns to the matrix. 

In your requirement, q1, q2 and June are not columns in one dimension, so dynamic addition is not possible. 

Now I have a simple example for you. 

First you need to customize the matrix. We need to create two tables of our own to hold the columns of the matrix: 

Then create a measure for the rows of the matrix. The value of 5 and 6 and 7 in DAX can be replaced with the aggregated values you need to calculate: 

M_2 = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Columner'[option] ) = "Innovations", 5,
    SELECTEDVALUE ( 'Columner'[option] ) = "Loyalty", 4,
    SELECTEDVALUE ( 'Columner'[option] ) = "Value", 6
)

Then create a measure for the columns of the matrix, where it compares the rows and columns one by one: 

M_1 = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Header'[level] ) = "quarter",
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( 'Header'[option] ) = "q1", [M_2],
            SELECTEDVALUE ( 'Header'[option] ) = "q2", [M_2],
            SELECTEDVALUE ( 'Header'[option] ) = "q3", [M_2]
        ),
    SELECTEDVALUE ( 'Header'[level] ) = "month",
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( 'Header'[option] ) = "may", [M_2],
            SELECTEDVALUE ( 'Header'[option] ) = "june", [M_2]
        )
)

This is the final result: 

I hope this example helps you. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

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

[BeaBF]

@Jithin00 Hi! can you paste the dataset under this matrix?