How to filter on multiple columns in single table

Question

i have one requirement:

list of columns like(Reason1,Reson2,.....,Name,Calimid,Patient)

i have 10 columns(Reason1, Reason2,Reason3,Reason4........)  in single table. but conditions  reason1 =2 , for the claimid count or claimid values. i want show only 2 for all 10 columns.

Note: we need to show only Reason 2 claimid list,plz create any dax cal?

Comments

Thank you so much.

first question is by using date diff create a weekdays possible or not?

ex: 20/09/2018 and 2/04/2016 (2 difference dates)

---

Glad to help @vnk.

I have no idea about it. But you can refer to this.

https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662

---

1)How do you Implement Incremental Refresh in Power BI and what are the necessary in services. explain details.

 - incremental refresh

- Fully Load

---

@vnk, 

https://www.edureka.co/community/58828/how-to-implement-incremental-refresh-in-power-bi

You do not need any services. If the number of rows in the dataset is within the maximum number of rows in Power BI Desktop, then the dataset can be fully loaded else you need to either filter or load a subset of the dataset. 

To load large data during incremental refresh, Power BI Desktop makes use of range parameters. Find the complete instructions here.

---

Thanks,

i have 2 requirements in power bi

1) in desktop User select  May Month it is showing  Previous 3 Month.how to create dax calculation?

Ex: Select May-- Display (March, Apr, May)

2) IN Service,Filter Month- Every Month automatic display the current Month , how to create?

Ex: now Current Month is Oct- Next Month is November; But November Month it is automatic showing  Nov month, user Not select the Nov month.is possible or Not?

Answer 1

I used dummy data to show how to get the output like yours.

Dummy Data -  I took 4 Reasons to create a table.

Now Load the data into Power BI.

Then Click on All reason columns and click on unpivot columns to collect all reason codes in one Reason columns and Reason Codes field.

Like below - 

Apply and close the editor, Go to Power BI Desktop. Follow below steps:

1. Select table from Visualizations.

2. Select Reason codes, Claim_id and amount columns.

3. Click on arrow button next to Reason codes and select Dont Summarize instead of Sum.

4. The table will be shown as below.

Comments

The table shows claim_id for every reason code and distinct claim_ids. If any changes, please do ask for them.

---

Thanks for your help. i have doubt on %. (In power bi apply grand total of percentage but it is showing 100% more) like- i have 4 bars-62.03%,40.7%,13.24%,2.76%.(wrong). i am taking distinctcount function.

for example,

status code, Distinctcount(Claimid)

1,43402

22,3475

1,2345

44,560

% cal?

But link the table for Mapping relationship for 3 table having many to many relation is there.

---

What field are you showing in %?

---

Use count instead of Distinct count function and then show value as percent of grand total.

See below image - 

Both would show total as 100 cause you selected show percent as grand total, but the values would change depending on the function you select.

1 - Distinct count function

2 - Count function

Does this solve the problem?

---

status code, Distinctcount(Claimid) (total 100% then each individual bar%)

1,  43402

22,   3475

1,    2345

44,  560

% cal?----Statuscode wise percentage?

---

I have applied grandtotal on the bar graph, it is showing % wrong, i want % on below req.

Q) status code, Distinctcount(Claimid) (total 100% then each individual bar%)

1,  43402

22,   3475

1,    2345

44,  560

% cal?----Statuscode wise percentage?

---

Let me give an example - 

Suppose 5 claim id with 4 types of codes

Claim id	Status Code
AA	1
BB	1,5
DD	2
GG	1,6
FF	5

The table of status code by count and discount function would be like below

Status Code	Count of claim	Distinct count of Claim
1	2	2
2	1	1
5	2	2
6	1	1
Count of Claims	6	5

If you Convert the above to Percent of Grand Total. After converting them as % will be like below -

Status Code	% of Count of claim	% of Distinct count of Claim
1	2/6 = 66.66	2/5 = 40
2	1/6 = 33.33	1/5 = 20
5	2/6 = 66.66	2/5 =40
6	1/6 = 33.33	1/5 = 20
Grand Total %	100%	100%

Where 40+40+20+20 = 120 != 100

So do you understand why the total % is >100 %. 

---

You should use count of claim id/ total number of claim id. Else % will be greater than 100%

---

we  are facing Many to Many Relation ship in power bi:

we have 3 tables: Header(Mastercontrole,Payer,Date), Claimmain(Mastercontrole,Claimid,Billedamount,Paidamount), Linedetails(Mastercontrol,claimid,allwodamount,Reason1,reason2) tables

her relation ship=Mastercontrole to Mastercontrole and claimid to claimid

But distinctCount(Claimid),payer--but graph--payer by count

and  Reason1&2(filter 22 only count) 22claimidcount. totalReason1&2 for the 22 count(500)

i need payer by reason22count(500-this is distinct count), but in bargraph-is is showing wrong count, why because many to many relationship. how to resolve this?

---

Do you want to remove many to many relationship between tables?

---

No,we need to find the many to one relationship.but the count is matching for payers.

---

Hi, you can refer to this blog to deal with many to many and with many to one relationships.

https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships

I do not know much about Many to many relationships in Power BI. It might help you.

---

thanks for your reply. NEw requirement:

i have one table, in table columns contains Master,claimid,billed,paid,servicecode,reasoncodeA1,AmountA1,quanityA1,,...etc we have 9 reasoncoden,Amountn,Quantityn coumns data available. see below columns

Master,Claimid,billed,Paid,servicecode,reasoncodeA1,AmountA1,QuantityA1,ReasoncodeA2,AmountA2,QuantityA2,ReasoncodeA3,AmountA3,QuantityA3,ReasoncodeB1,AmountB1,QuantityB1,ReasoncodeB2,AmountB2,QuantityB2,ReasoncodeB3,AmountB3,QuantityB3,ReasoncodeC1,AmountC1,QuantityC1,ReasoncodeC2,AmountC2,QuantityC2,ReasoncodeC3,AmountC3,QuantityC3.

123,10160623,400,200,Hv:2309,45,100,2,125,120,3,null,-120,4,22,10,1,125,400,20,null,null,null,2,null,1,45,100,2,16,1000,4

234,10160645,500,600,sv:28902,16,100,2,null,120,3,2,-120,4,125,10,1,45,400,20,46,200,null,16,null,1,167,100,2,35,1000,4------50 rows

example_output: Reasoncode by Claimidcount

we need Reasoncodes,Claimid

Null ,   10160623,10160645          

45 ,10160623 

125,10160623,10160645

22,10160623

2 ,10160623,10160645

16,10160645,10160623

167,10160645

 35 ,10160645 

46,10160645           

in power bi how to combine multiple columns in to single column, we do unpivot ..etc.

can you please correct answer.                 

---

Your question is converted as a new question. Kindly visit the below link.

https://www.edureka.co/community/52199/how-to-combine-multiple-columns-in-to-single-column