Appending tables but missing a year in visual

[samoberoi]

Hi
I am trying to show data of 3 tables of different years like 2018, 2019 & 2020 in a visual. I appended these 3 tables and did the count of ID's for these 3 years to showcase in one visual. Instead of showing 3 years in the chart, it only shows 2 years as 2019 & 2020 but doesn't show 2018 and also shows the header of the charts in the visual, which also i don't want.

Can someone help me with this please?

Apart from that i am also trying to use this measure to count number of ABC ID's in England in 2018, 2019 and 2020 after appending the 3 tables together, but this measure doesn't seem to be working when i put that on the table visual with country names. The measure i am trying to use is as "

Count ABC England = CALCULATE(

    COUNT(Append1[ABC_ID]),

    Append1[COUNTRY] = "England" &&

    Append1[ACY_ID] = 2018 || Append1[ACY_ID] = 2019 || Append1[ACY_ID] = 2020)

Some help will be much appreciated.

Thanks

Sam

[PVO3]

Problem #1: your appending of tables is problably incorrect. Make sure the order & names of columns matches.

Problem #2: 

Count ABC England = 

CALCULATE(

    COUNTROWS(Append1),

   Append1[COUNTRY] = "England" && Append1[ACY_ID] IN {2018,2019,2020} 

)

[samoberoi]

Hi PV03,

Thanks for the solution. The append table alteration really worked, but the measure still isn't working. How can i show the same type of chart for different countries in different years, but by DAX calculation and not by putting the column names on x axis, y axis legends etc on the chart.

Thanks again

[PVO3]

Not really sure what you would like to achieve. Please provide an example.

If I would guess what you're saying is that you would like different visuals that are identical, except for the country they show. In that case you select the visual and add country & year to the filters. Then you can simply select what country & year to display.

Alternatively, you can use the measure i provided. This will show the amount of rows for England in 2018, 2019, 2020. But each visual will need another measure.

[samoberoi]

Hi 

No, sorry for the confusion. I would like the same visualisation as follows, but with DAX calculation instead of putting the column names on Axes or legends etc.

The measure i tried before, which doesn't work, is as: 

CALCULATE(

    COUNT(Append1[ART_ID]),

    FILTER(Append1,

    Append1[COUNTRY]="England"&&

    Append1[ACY_ID] IN {2018,2019,2020}))

Thanks

[PVO3]

The part really confuses me is that you would like a "DAX calculation instead of putting the column names on Axes or legends etc." 

- Are you looking for a measure or a calculated column?

- What fields are you still willing to add to your visuals? 

- Why would you not want to add legends and/or axis fields to your visual?

[samoberoi]

Hi
Sorry again for the confusion. You can imagine that i am new to Power BI usage. To try  clearing the confusion again what i meant to say is that in the visual i sent before, i just put the fields on x axis and Y axis etc and it created the visual of which i sent you the snip before. Now, instead of simply putting the fields like years on x axis and country on Y axis etc etc, can i not just create a measure which will show the visual the same way as before showing how many customers were there in 2018, 2019 & 2020 in England and how many were in wales etc. 
Hope i could be able to explain it a bit more properly this time.

Regards

[PVO3]

Sorry but I don't get it. You would like to remove fields from this visual and instead use a single measure, but still show the exact same result? I can't see any benefit from this and is not possible. I'm afraid you're mixing things up.